Help! Must determine max number on text field

[mitchem1]

Is there anyway to determine the maximum Permit Number given the following Permit Numbers?

7E-994
7E-3546A
7E-110
7E-25
7E-887A
7E-36B

The number I need to display for the user is 7E-3546A, but since this is a text data type, the Max function returns 7E-994. The Last function doesn't work because permits are not always assigned in order. I have tried various combinations of Len, Right, Mid, etc., but have been unsuccessful so far. Any ideas would be greatly appreciated.

 

[The_Doc_Man]

Without a text parser, you have a massive problem. I'll be blunt. You cannot get where you want to go directly from here in a single, simple step. The wildly variable nature of the assignment numbers is going to nail you. The way I would do this is to write a field "splitter" subroutine to take out that 7E- prefix, leaving me with either a string of digits or a string of digits followed by an alphabetic character. Then I would scan the characters one at a time to see if they were alphabetic or numeric. I would keep the digits but toss everything else.

Then I would convert the digits using CLng( digit-string ) to convert the number string to a LONG integer format. Once this convertion is complete, you could determine the MAX pretty easily by using a DMax aggregate function or a totals query with a Max query function.

 

[mitchem1]

Thanks for the reply Doc_Man. I was hoping I was wrong, but had assumed that this would be a pain in the rear. I can get rid of the 7E- prefix, but how do you scan the characters one at a time to see if they're alpha or numeric?

 

[mitchem1]

Thanks Pat.

 

[The_Doc_Man]

Please do not get into the habit of posting the same problem under multiple threads.