MS Access VBA - Query String to find Max Alphanumeric Value

[ironfelix717]

Hi,



Having trouble with designing a query where the returned value will be the maximum of an alphanumeric string in the format off.

ID (short text)
1234X - Where I need to select the max ID, where the first 4 chars are number and the last character may be a letter.


Example of return results:
1021
1022
1023
1023A
1023B

Max = 1023


Thanks for any help.

Regards

[ironfelix717]

SOLVED:

Turns out using DMax function works find in my case.

Awesome.
Easy fix.


Thanks

[ironfelix717]

Update:

DMax encounted a problem when the digits increased.

DMax worked fine for....

ID    (note:  this datatype is short text)
1001
1001A
1002
1003
1004
1004A
1004B

DMax = "1004B" (note: use a left or right function in VBA to trim to just "1004")

However, when the table was....

ID
50000  (Note: For some reason Access puts the largest numerical data type at the top of the table - even a simple sort query does not fix this)
1001
1002
1003
1004
1004A
1004B

DMax = "1004B" (This is a problem)

So, i remedied the issue with creating a query with two fields. The first field is, of course, ID, the second field is an expression which converts the ID to just the numeric portion. The expression is "Sorted: Val([ID])".

So, the query datasheet looks like...

ID                          Sorted
50000                   50000   (this value is STILL at the top regardless of sorting my ascending order....)
1001                     1001
1002                     ....
1003                     ....
1004                     ....
1004A                   1004
1004B                   1004

Now that the "sorted" query column/field exists, the DMAX will return as...

DMax = 50000

As it should!

A lot of work and messing around for something so simple. .. Hopefully this helps someone. Note: the VAL function will not work for format of A1234, and will only work for 1234A, where the alphabetical characters are the SUFFIX. Val(A1234) will return null or 0.