How do I make the Primary Key autonumber as date and time

[Guest]

When I'm creating a table, I'd like to make the Primary Key an autonumber
that is the date and time the record is added ie. yymmddhhmmss

I don't want this number to change once it is entered automatically. Is
this possible?

 

[Duane Hookom]

This would not be an autonumber. You can create a field with a default value
of Now() and never expose it to users. This could be the primary key if you
want. I would be somewhat concerned about rounding.

 

[John Vinson]

When I'm creating a table, I'd like to make the Primary Key an autonumber
that is the date and time the record is added ie. yymmddhhmmss

I don't want this number to change once it is entered automatically. Is
this possible?

Well, certainly not as an Autonumber. Autonumbers are either long
integers or GUIDs, not dates and not controllable.

I'd be very leery of using a date/time value as a PK. It would FORBID
you from ever using an Append query to add data to the table (since
you'll get more than one record added per second); if you have a
multiuser system, you'll also risk two users happening to insert
records at the same time. It also violates database design principles
to some extent, since you're using the one field for two different
purposes.

If you really want to do this anyway just set the field's data type to
Date/TIme, its DefaultValue property to =Now(), and its format to the
string you specified. It will actually store a Double Float number but
display it as you describe. Alternatively, define the field as being
of Text type (that's too big for a long integer) and use the Form's
BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me.txtTimeStamp = Format(Now(), "yymmddhhnnss")
Me.Dirty = False
End Sub

John W. Vinson[MVP]

 

[Jamie Collins]

I'd be very leery of using a date/time value as a PK.

It is very common for a DATETIME column to to comprise a composite key
with an identifier from another domain e.g. the nonsequenced candidate
keys on a PayrollHistory table would be:

(employee_number, start_date)
(employee_number, end_date)
(employee_number, start_date, end_date)

I do have one table with a single-column key of type DATETIME: its name
is Calendar ;-)

If you really want to do this anyway just set the field's data type to
Date/TIme, its DefaultValue property to =Now(), and its format to the
string you specified. It will actually store a Double Float number but
display it as you describe.

Sure, and a MEMO column gets stored as 1s and 0s <g> but notionally
it's still a MEMO column. Such is the joy of a strongly typed language
such as SQL: it doesn't matter how a software vendor chooses to
implement a DATETIME (actually called TIMESTAMP in the SQL standard),
it's still a DATETIME.

Jamie.

--