Unique ID Number

[KC Classic]

I am trying in Access 2003 to create a user-assigned unique ID number for a
database. The number would need to be uneditable and be assigned each time a
new record was added to the table based on the day the record was added. I
would like the number to appear as follows:


031308 01
031308 02
031308 03
031408 01
031408 02
031501 01
031501 02
. . . .

I would like for this to be the primary key of my main table supplying
information for additional queries, forms & reports. Does anyone know an
easy way to accomplish? Any help is appreciated.

Thank you.

 

[Wayne-I-M]

Hi

You should format a table field as an autonumber.

Note that nuber don't start with 0
so
031308 01
031308 02
031308 03
are text

 

[Wayne-I-M]

So I have just re-read your post. Is the first section of the "number" the
date.

If so you can use Dmax funtion to find to highest number for the date -
format the date to a string to get the 1st section of the "number"

 

[KC Classic]

Thanks Wayne,

One more question though. Do I somehow format the "date" portion of this
field under the General tab under Format. For instance, "mmddyy" + ....

Not very good at this type of coding in Access.

 

[Ron2006]

Another note,

If you are trying to use the date in the creation of that number, you
should Probably change the format. to be yyyymmdd.

using the format you have, next march records will be intermingled
with this years (and all years march records) and you will have a
tough time separating 2009 records from 2008 records since the
differentiation is in the middle of the number.

And then the y2k problems highlighted the fact that you should
probably always use the 4 digit year just to save your grandkids
problems when they try to figure out these old systems that are
outgrouths of your system and the designer forgot about the change in
century problem.

Ron

 

[BruceM]

Assuming the date is part of the record, you don't need to store the date
again. Instead, increment the second part of the number. I will use the
name SequenceNumber for the number (Long Integer) field in which the
incremented number is stored. I will call the table YourTable, and the date
field YourDate. In the After Update event you could have:

If Me.NewRecord Then
Me.SequenceNumber = DMax("[SequenceNumber]", _
"[YourTable]", _
"[YourDate ] = #" & Me.YourDate & "#") + 1
End If

In an unbound text box on the form you could have the Control Source:
=Format([YourDate],"mmddyy") & " " & Format([SequenceNumber,"00")

The combination would be unique, but you would still need a primary key
since the SequenceNumber field will contain duplicates. An autonumber field
would be a simple way to add a primary key.

 

[BruceM]

I should have specified the After Update event for the text box bound to the
YourDate field.

Assuming the date is part of the record, you don't need to store the date
again. Instead, increment the second part of the number. I will use the
name SequenceNumber for the number (Long Integer) field in which the
incremented number is stored. I will call the table YourTable, and the
date field YourDate. In the After Update event you could have:

If Me.NewRecord Then
Me.SequenceNumber = DMax("[SequenceNumber]", _
"[YourTable]", _
"[YourDate ] = #" & Me.YourDate & "#") + 1
End If

In an unbound text box on the form you could have the Control Source:
=Format([YourDate],"mmddyy") & " " & Format([SequenceNumber,"00")

The combination would be unique, but you would still need a primary key
since the SequenceNumber field will contain duplicates. An autonumber
field would be a simple way to add a primary key.

I am trying in Access 2003 to create a user-assigned unique ID number for
a
database. The number would need to be uneditable and be assigned each
time a
new record was added to the table based on the day the record was added.
I
would like the number to appear as follows:


031308 01
031308 02
031308 03
031408 01
031408 02
031501 01
031501 02
. . . .

I would like for this to be the primary key of my main table supplying
information for additional queries, forms & reports. Does anyone know an
easy way to accomplish? Any help is appreciated.

Thank you.