Using SELECT MAX with alphanumeric values.

J

John

Hi,

Problem I am having is that I am trying to SELECT MAX in a field with IDs
like 'SC1', 'SC2'... Problem is that 'SC9' is seen as greater than 'SC10' or
'SC11'. Can I edit the query or the table settings so that the proper ID is
selected?

Thx.

John
 
C

Chris

Try ...

Select max(left$([yourfield],2) &
format$(mid$([yourfield],3),"00000") as PaddedNumber
from ...

use as many "00000"'s as you need to ensure that all
numbers are the same length
 
A

Allen Browne

The problem occurs because the data is not atomic, i.e. the field contains
two things: the prefix and the number. Break the field into two: one for the
prefix, and one for the number. Access will then be able to find the correct
data very fast (using indexes) and very simply.

If you cannot do that, you will have to parse the field into two calculated
at the query level. This example assumes the prefix is always two
characters, and no fields contain Null:
MyPrefix: Left([MyField])
MyCounter: CLng(Mid([MyField],3))
 
G

Guest

Actually I was wrong. You have to convert the last numbers to integers
otherwise it will read 1 as higher than 20

testcode is the tablename, code1 is the field your getting a max from:

select max(cint(mid(code1,3,len(code1)-2))) from testcode
[email protected]
 
Top