Find the missing numbers

[janjau]

I have a table named:tblIntCod e, column name: interestcode, data type is Text. I have some missing interest code and i want to reuse those missing code. Example: P123, P125, N479, N482,.... I need to use the missing code P124, N480, N481...What I need is a query that can generate the code P124 or N480 in one entry.
The codes are combination in Alpha numeric,(e.g., P123, IB012)and a maximun lenght of 5 characters. Thanks!!!

JJ

[Rabbit]

You'll have to use VBA unless you create a table with the numbers from 0 through 999 and a table with each alpha type.

If you use VBA, you'll just need a loop to loop through and check the numbers from 0 to 999 for each alpha type.

If you use create the tables, then you can use SQL to cross join the new tables and then outer join that to your table.

[ADezii]

Are we talking about 26,000 possible Codes, namely:

A000, A001, A002...Z000, Z001....Z997, Z998, Z999

[Rabbit]

More, you have have up to two alpha characters. But I assume the alpha characters have a meaning and so it limits them down to just a handful. But that's just an assumption.

[NeoPa]

JanJau:
I have some missing interest code

Interesting, but nowhere do you explain what a missing interst code is nor how one would recognise one within the structure of your database. This leaves the question all but pointless - hence experts are trying to guess what your question should have been (NB a prime candidate for reporting) instead of being in a position to give a helpful answer.

Please consider providing the basic information required in order for any response to be helpful.