Query help - missing something simple

[jbphoenix]

I have an ID field that is text. Here are some example ID's. The number is generated automatically and the letter is added by the user.
A10565
52073
C20633
RMA18941

I need to start numbering new id's automatically. I don't want to repeat numbers so I need to know which numbers have been used. So I am trying to remove the letters preceeding the numbers. However I can't seem to get rid of the records with more than 1 letter. Here is the what i've tried in the query -

test2: Left([ID],3)
newnum: IIf([test2]="A" Or [test2]="C" Or [test2]="F" Or [test2]="H" Or [test2]="R" Or IIf([test2]="RMA",Mid([ID],4),[ID]),Mid([ID],2),[ID])

after that is run this is what i get -
10565
52073
20633
MA18941

They are fine except the last one - MA18941 needs to be 18941.

I know I am missing something simple

 

[FoFa]

Truthfully, I would just create a function to return the numeric portion of it in VBA.

 

[jbphoenix]

The field is set as text - would that make a difference in VBA?

 

[Moniker]

Look at post #3 in this thread. I wrote a function to do just what you're after.

 

[The_Doc_Man]

My thought is a little more heretical. When you let users do what they want, they... do what they want whether you liked it or not.

I would force all aberrant user inputs (and the good ones, too) into a two-part field. Part 1, text. As many characters as you allow. Part 2, the number. Make the key a compound key. Now... here is the heresy. Once you have everything split into text and numeric, force the text to be reset so that the abomination cases (with too many letters) are removed. Replace these cases with fewer letters to conform to what you wanted. Live with the fact that some of the keys change.

If you are planning to rely on key structure down the road then it is time to weed out the slop now. If you aren't willing to remove the slop, then don't bother to change anything at all. Live with what you have. Don't find some sloppy workaround that will end up bastardizing your algorithms because of a few oddball cases.