autonumber question

I want my auto number to be in the form of todays date and then the record
number e.g., 10070901. If I enter a new record tomorrow, I want it to start
with tomorrows date and start at 01. Is that possible?

On Wed, 7 Oct 2009 13:45:01 -0700, JOM wrote:

I want my auto number to be in the form of todays date and then the record
number e.g., 10070901. If I enter a new record tomorrow, I want it to start
with tomorrows date and start at 01. Is that possible?

No, not with an Autonumber; an autonumber has one purpose only - to provide a
meaningless unique key.

And it's a Bad Idea anyway. A date *IS DATA* and should be stored in a date
field, as data, not embedded in the Primary Key of your table. A field should
be "atomic" - containing only one piece of information; and a primary key
should emphatically not depend on the value of any other field in the table.

If you want to just display this composite, you can use a date field to store
the entry date (just set its DefaultValue property to =Date() ), and a Number
field which you can increment in your form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!SeqNo = NZ(DMax("[SeqNo]", "yourtablename", "[Datefield] = Date()")) + 1
End Sub

and concatenate them for display purposes. I'd use an Autonumber as the
primary key, but - if you insist - you could make this date field and the
SeqNo field a joint two-field primary key.
--

John W. Vinson [MVP]

As it happens, I agree with John on this ... a unique record identifier does
only that - uniquely identifies a row.

But just so you know, some folks feel that there are unique "natural" keys,
pieces of data that are both unique AND provide data about the table's
topic.

As an example, if you had a way of ensuring that your table would NEVER have
more than one record added at the same exact instant in time, a date/time
field filled using the Now() command could provide a unique identifier.

Careful who you bring this up with, though, as the subject of "natural" vs.
"arbitrary" primary keys is a matter of religious preference for some...

Regards

Jeff Boyce
Microsoft Access MVP

"JOM" wrote in message
...
I want my auto number to be in the form of todays date and then the record
number e.g., 10070901. If I enter a new record tomorrow, I want it to
start
with tomorrows date and start at 01. Is that possible?

On Wed, 7 Oct 2009 15:59:32 -0700, "Jeff Boyce" wrote:

Careful who you bring this up with, though, as the subject of "natural" vs.
"arbitrary" primary keys is a matter of religious preference for some...

HERETIC! BLASPHEMER! getting out the torches and pitchforks

g
--

John W. Vinson [MVP]

Coming from you, John, I take that as a fine compliment!

Regards

Jeff Boyce
Microsoft Access MVP

"John W. Vinson" wrote in message
...
On Wed, 7 Oct 2009 15:59:32 -0700, "Jeff Boyce"
wrote:

Careful who you bring this up with, though, as the subject of "natural"
vs.
"arbitrary" primary keys is a matter of religious preference for some...

HERETIC! BLASPHEMER! getting out the torches and pitchforks

g
--

John W. Vinson [MVP]

On Thu, 8 Oct 2009 08:24:46 -0700, "Jeff Boyce" wrote:

Hey, I'm a heretic and a blasphemer too!
(i.e. that's how I intended it)

Coming from you, John, I take that as a fine compliment!

Regards

Jeff Boyce
Microsoft Access MVP

"John W. Vinson" wrote in message
.. .
On Wed, 7 Oct 2009 15:59:32 -0700, "Jeff Boyce"
wrote:

Careful who you bring this up with, though, as the subject of "natural"
vs.
"arbitrary" primary keys is a matter of religious preference for some...

HERETIC! BLASPHEMER! getting out the torches and pitchforks

g
--

John W. Vinson [MVP]

--

John W. Vinson [MVP]