How do I start a Unique ID with my own prefix?

[James Taylor]

Hi All,

I need to begin my unique id string with A2003 and then add whatever numeric
sequence gets assigned after that.

For instance, the first number should be "A20031", the second "A20031" etc

What is the best/easiest way to do this?

Thank you!

James

 

[Larry]

-----Original Message-----
Hi All,

I need to begin my unique id string with A2003 and then add whatever numeric
sequence gets assigned after that.

For instance, the first number should be "A20031", the second "A20031" etc

What is the best/easiest way to do this?

Thank you!

James


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003


.
use an autonumber field to create the autonumber.

create an update query to add you unique tag to it.
If you autonumber is "ID" and your unique tag is "A"
and it looks like your adding the year to it so your
criteria in your query would look like this:
"A"&format(date,"yyyy")&[ID]
date would be the field name of the date in your table

 

[Wayne Gillespie]

Hi All,

I need to begin my unique id string with A2003 and then add whatever numeric
sequence gets assigned after that.

For instance, the first number should be "A20031", the second "A20031" etc

Shouldn't the second be "A20032" ??

What is the best/easiest way to do this?

Thank you!

James

Watch for wordwrap

Assuming that the 2003 represents the current year then -
The following code will make the ID for the first record entered each year 1 and then increment from there.
Change [ID] to the name of your id field and "Table1" to your table name.

NewID = "A" & Year(Date) & Nz(DMax("CLng(Right([ID],Len([ID])-5))","Table1","Clng(Mid([ID],2,4))=Year(Date())"),0)+1

A20031
A20032 ...

A20041
A20042 etc


Wayne Gillespie
Gosford NSW Australia

 

[James Taylor]

etc

Shouldn't the second be "A20032" ??

WHOOPS, YES, sorry...brainfade this AM.

Watch for wordwrap

Assuming that the 2003 represents the current year then -
The following code will make the ID for the first record entered each year

1 and then increment from there.

Change [ID] to the name of your id field and "Table1" to your table name.

NewID = "A" & Year(Date) & Nz(DMax("CLng(Right([ID],Len([ID])-5))","Table1","Clng(Mid([ID],2,4))=Year(D
ate())"),0)+1

A20031
A20032 ...

A20041
A20042 etc


Wayne Gillespie
Gosford NSW Australia

 

[James Taylor]

Thanks Wayne and Mike!

James Taylor

 

[John Vinson]

Just note that this kind of "Intelligent Key" is generally frowned
upon. Storing two (or perhaps three, if the A is meaningful) pieces of
data in a single field is bad design.

For one thing, the text string A200310 will sort before A20032 and
after A20033. Since the 10 is not a number and not in its own field,
your records will come out in alphabetical order, not "numeric" order!