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

  • Thread starter Thread starter James Taylor
  • Start date Start date
J

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
 
-----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.
Click to expand...
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
 
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
Click to expand...

Shouldn't the second be "A20032" ??
What is the best/easiest way to do this?

Thank you!

James
Click to expand...

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
 
Wayne Gillespie said:
etc

Shouldn't the second be "A20032" ??
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
 
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!
 
Back
Top