Generate an ID

[geo]

I should tailor the dress based on the customer
requirements. This is the way he wants. So let it be with
your help guys. Hope so.

How can I, programmatically, generate an ID in Ms Access
using, in each time, a combination of 3 alphabets from A
to Z with 3 Numbers from 0 to 9
Example: AAA-001, AAA-002, ABA-001, ABB-001, .., etc.

Thanks for your help.

 

[John Vinson]

I should tailor the dress based on the customer
requirements. This is the way he wants. So let it be with
your help guys. Hope so.

How can I, programmatically, generate an ID in Ms Access
using, in each time, a combination of 3 alphabets from A
to Z with 3 Numbers from 0 to 9
Example: AAA-001, AAA-002, ABA-001, ABB-001, .., etc.

How do you decide which to increment? Do you run from AAA-001 through
AAA-999 before generating AAB-001, or what?

This will require some not terribly difficult VBA code in the Form's
Beforeinsert event, but I hesitate to post sample code without knowing
just how this field is supposed to work!

 

[geo]

thks for replying john
the idea is to use these combinations as car plates.
so, when the code is put to work, it should give me, at
once, a list of all possible combinations starting from
AAA-001 ending with ZZZ-999. this would lead to expect
many many combinations.

hope i made it clear enough

 

[John Vinson]

thks for replying john
the idea is to use these combinations as car plates.
so, when the code is put to work, it should give me, at
once, a list of all possible combinations starting from
AAA-001 ending with ZZZ-999. this would lead to expect
many many combinations.

17576000 to be exact... rather more than you could or would ever want!

If that's REALLY what you want - a 17,576,000 row table - you could
write some VBA to do so; or you could create two little tables: Alpha,
with one 1-byte text field L, manually filled with the values A to Z;
and Num, with one integer field N, filled with values 0 to 9. A query

SELECT Alpha.L & Alpha_1.L & Alpha_2.L & "-" & Num.N & Num_1.N &
Num_2.N
FROM Alpha, Alpha AS Alpha_1, Alpha AS Alpha_2, Num, Num AS Num_1, Num
AS Num_2
INTO Platenums.Plate;

Start this running and check again the next morning... and then
compact your database!

 

[Guest]

John,
i created the two tables, the select query sql statement
gave me an error "syntax error in FROM clause"
what is likely to be the problem? i am using xp in both
office and win.
thks.

 

[John Vinson]

John,
i created the two tables, the select query sql statement
gave me an error "syntax error in FROM clause"
what is likely to be the problem? i am using xp in both
office and win.

Please post the table names and copy and paste the actual SQL string
you're using.