Dmax problem

[Guest_imported]

hi

I don't know if there are other functions to use....

myfield
-------
10000
A10212
B21540
10005
10001
10002
C45766

I need to know what is the largest number without the alphabet in front....in this case the answer should be 10005. Dmax gives me a type mismatch error, i guess it is due to some record with the alphabet.

Please help, thank you.

 

[KenReay]

Hi

Is the alpha character (if present) always the fisrt character?

If yes then make a query based on the table with a calculated field n:IIF(IsNumeric(Left([youcol,1)),[youcol],mid(yourcol,2)).

Use the query as the source of the DMax and the column 'n', or Order By Descending, open the record set and read the first row.

Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use