Unique ID...

[yessir]

I wish to generate unique id's with letters and numbers,

starting with AA001

and then going up, so that there are always 2 letters and at least 3 numbers, this is to be used as my primary key as well,

how can i do this?

 

[Fizzio]

The only real way is to use VBA to set the ID but how this is done depends on your sequencing ie does it go

AA001 -> AA002
AA001 -> AB001
BA001 -> AB002
etc.

 

[yessir]

sequence

AA001
AA002
:
:
:
ZZ999
AA0001
:
:
:

 

[dcx693]

Seems like an odd way to sequence them. Why not let
ZZ999
roll into
AA1000

It's easier mathemetically to program for.

 

[yessir]

true, what ever works!

true, what ever works!

the key is how do i get it to work?

 

[dcx693]

Some ideas:

Use a VBA function that checks 3 things:
1 - for the first alpha character
2 - for the second alpha character
3 - for the numeric portion

When the numeric portion exceeds a certain number (which you set), part (2) increments. When part (2) exceeds the letter Z, you increment part (1). You'll have to decide how many numbers you can assign to part (3).

The custom function should check the last assigned value. If these are going to be assigned in order, with no possibility of going back to a previously deleted ID, try using DMax.

To get the the initial values like A,A,1 to format to AA001, use the Format function like this: "AA" & Format(1,"000") .
Even if your number field exceeds three numeric digits, the Format([number field],"000") will still produce numbers with at least three digits, as you require.