Question Locked

How can I Generate an alpha-numeric Unique ID in MS Access

0 helpful
6 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

How can I automatically assign each record with a unique alphanumeric identifier (e.g. AWAZ0001) when a record is first created. The first 4 alpha characters are pre-defined in a field of their own and ideally, the 4-digit numeric part is a date identifier as to when the record was created.

I would appreciate any suggestions as to how to go about accomplishing this. I would also settle for some alternative in automatically assigning some kind of unique alphanumeric identifier.

6 replies

by tintoman

of creation as part of your ID it is not going to be unique if you create more than one on the same day is it?
In this case I would assign some code to the "afterupdate" property of one of the other controls, perhaps the customer name or whatever.
Then code something like
Me!MyIdControl.Value = Me!ID

This is assuming you have a field on your data table called "ID" which uses the Autonumber function to apply unique numbers to each record.
You can manipulate the Autonumber result to some degree, for example
If Me!ID < 10 then
Me!MyIdControl.Value = "000" & Me!ID.Value
End If

And so on...

by asifshehzad

I just want to creat an ID which stat with four alphabet "AWAZ" and four Numerics "0001" the ID must be like "AWAZ0001" and for the nex record "AWAZ0002" and further on

So please if you help on this

by tony hopkinson

You'd have t0 either do
select Max(Num_Part) From SomeTable Where AlphaPart = 'AWAZ'
Add 1, then format it to put in the leading zeros, then use it.

or Create a table keyed by the prefix with next number in it.
select from that add 1 put it back then format it and use it.

Do the numbers have to be sequential within prefix?

if
AWAZ000001
BQEF000002
AWAZ000003

is okay then make num_part an identity column and then format it and prefix to get the output.

Why do you want to do this? If it's a requirement fine, if it's a solution, rethink it.

by mafergus

Just take advantage of the existing identity field to autogenerate the numeric portion and then simply append your date code when pulling up the record?

by tony hopkinson

If it is going to be unique just key the table with the prefix and date_created.

Or add an integer id with identity and keep prefix and date created as information.

Don't go down this intelligent id route, it's dumb...

This discussion is closed to new replies.