Return the Max Value from Alphanumeric records

[Arlend Floyd]

ClientID
-----------
ABC1
ABC2
ABC3
ABC4
ABC10

I want to return the Max numeric record after the prefix of "ABC" I want to
return ABC10 from the table.

Thanks,

Arlend

 

[Allen Browne]

Have you thougt about what data might actually be in the field?

What would be the max of values like this:
A11BC
A1B3C
1AB4
01A0C0
A1 B 6
A2.77C

If the intention is to strip out all non-digits, you would need a function
like the StripNonDigit() on this page:
http://allenbrowne.com/CCValid.html

Or perhaps you have 2 different things in this field:
- a text prefix, and
- a numeric suffix
If so, the best solution might be to use 2 fields instead of stuffing 2
things into one field.

 

[John W. Vinson]

ClientID
-----------
ABC1
ABC2
ABC3
ABC4
ABC10

I want to return the Max numeric record after the prefix of "ABC" I want to
return ABC10 from the table.

Thanks,

Arlend

You'll need a calculated field:

SELECT * From Yourtable
WHERE ClientID =
(SELECT TOP 1 ClientID
FROM yourtable AS X
ORDER BY Val(Mid(X.ClientID, 4))
WHERE X.ClientID LIKE "ABC*")

 

[Arlend Floyd]

These are all account numbers and they are all the same "ABC" then numeric
form 1 to 100xxxxxxx. Is it possible to get the max numeric portion of the
account number even though its a text field?

thanks

 

[John W. Vinson]

These are all account numbers and they are all the same "ABC" then numeric
form 1 to 100xxxxxxx. Is it possible to get the max numeric portion of the
account number even though its a text field?

If the prefix is ALWAYS literally "ABC" - or some constant - then you're just
wasting space and making your job harder! You could have a Long Integer field
for your account number, and display it with a format

"ABC000000"

to *store* just the number - which will of course be easy to use for maximum
and other arithmatic operations - but *display* with the prefix and with
leading zeroes.