Using SELECT MAX with alphanumeric values.

  • Thread starter Thread starter John
  • Start date Start date
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
 
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
 
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))
 
Maybe use a mid starting at the third character. then compare that
select max(mid(fieldname,3,len(fieldname)-2))
(e-mail address removed)
 
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
(e-mail address removed)
 
Back
Top