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

By asifshehzad ·
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.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

If you are going to use the date

by tintoman In reply to How can I Generate an alp ...

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...

Collapse -

Not Using the date

by asifshehzad In reply to If you are going to use t ...

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

Collapse -

It will be a mess

by Tony Hopkinson In reply to Not Using the date

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?


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.

Collapse -

Why not

by mafergus In reply to Not Using the date

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?

Collapse -

What for ?

by Tony Hopkinson In reply to How can I Generate an alp ...

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...

Related Discussions

Related Forums