Autonumber? Not Sure

[Guest]

I would like to create a unique number for each record entered.
Automaticaly generated on "enter new record".

The format I need is "yymmdd" followed by "001".
i.e. 060115001. This has to be in one field not 2 seperate.

As records are entered for that day, the last three
numbers would change 002,003,004, etc. At midnight,
the number would change to 060116001.

I'm lost on this and am about to throw in the towel.
Any help would be appreciated.
Tom

 

[Jeff Boyce]

Tom

From a database design standpoint, it's considered not a good idea to shove
more than one "fact" into a single field. You may wish to see a value you
consider a "unique number" for each record, but that doesn't mean you need
to store the pieces in a single field.

If you already have a date field you use for capturing the date the record
was entered, you're nearly there! You'll also want a routine that comes up
with the next sequential number, starting over each day. Check the
mvps.org/access website for "Custom Autonumber" to find examples of how to
create a procedure that does that.

Now, for combining the two fields into one ... use a query.

 

[Guest]

Thanks for your advice, however
I don't understand all that they are
explaining on that web page.
I am very new to this.

The date control I understand.
If I just take the last three
numbers, Is there a procedure that I can
create, rather than a query, that
will start at "001" every day at midnight
and incrementally add "+1" when a "new record"
is created.

Thanks,
Tom

 

[Steve Schapel]

Tom,

Make the field that stores this sequential number a Number data type.
On the form that you use for data entry, put the equvalent of this in
the Default Value property of the control that is bound to this filed...


Nz(DMax("[YourSequentialNumberField]","YourTableName","[YourDateField]=Date()"),0)+1

When you need your "record number" displayed in the required structure
for your forms and reports, you can put the equivalent of this into the
Control Source of an unbound textbox...
=Format([YourDateField],"yymmdd") &
Format([YourSequentialNumberField]),"000")
.... or make a calculated field in a query, in a similar way, by typing
the equivalent of this into the Field row of a blank column in the query
design grid...
RecordNumber: Format([YourDateField],"yymmdd") &
Format([YourSequentialNumberField]),"000")

 

[Guest]

Thanks Steve,
The first part works Nz(DMax(...etc
I'm having problems with the unbound text box.
When I put the following formula in the Control
Source

=Format([TodaysDate],"yymmdd") & Format([RunNumber]),"000")

it is not showing anything in the control
on the form. When I go back to redo it, the expression
is not in the Control Source.

Tom

Tom,

Make the field that stores this sequential number a Number data type.
On the form that you use for data entry, put the equvalent of this in
the Default Value property of the control that is bound to this filed...


Nz(DMax("[YourSequentialNumberField]","YourTableName","[YourDateField]=Date()"),0)+1

When you need your "record number" displayed in the required structure
for your forms and reports, you can put the equivalent of this into the
Control Source of an unbound textbox...
=Format([YourDateField],"yymmdd") &
Format([YourSequentialNumberField]),"000")
.... or make a calculated field in a query, in a similar way, by typing
the equivalent of this into the Field row of a blank column in the query
design grid...
RecordNumber: Format([YourDateField],"yymmdd") &
Format([YourSequentialNumberField]),"000")

--
Steve Schapel, Microsoft Access MVP

Thanks for your advice, however
I don't understand all that they are
explaining on that web page.
I am very new to this.

The date control I understand.
If I just take the last three
numbers, Is there a procedure that I can
create, rather than a query, that
will start at "001" every day at midnight
and incrementally add "+1" when a "new record"
is created.

Thanks,
Tom

 

[Steve Schapel]

Tom,

My apologies, there was a typo in the expression I gave you, as there is
a rogue ) in there.

Are [TodaysDate] and [RunNumber] the names of your fields? Try like this...
=Format([TodaysDate],"yymmdd") & Format([RunNumber],"000")

 

[Guest]

Steve,
Got it! Now a little fine tuning.
For some reason, the count is stopping a 010
in the "text box" and 10 in the RunNumber
Max possible should be "999"
Here is the code in the "RunNumber"

Nz(DMax("[RunNumber]","DispatchTable","[TodaysDate]=Date()"),0)+1

Also, is there a way to have this number reset to 001 at Midnight?

Tom

Tom,

My apologies, there was a typo in the expression I gave you, as there is
a rogue ) in there.

Are [TodaysDate] and [RunNumber] the names of your fields? Try like this...
=Format([TodaysDate],"yymmdd") & Format([RunNumber],"000")

--
Steve Schapel, Microsoft Access MVP

Thanks Steve,
The first part works Nz(DMax(...etc
I'm having problems with the unbound text box.
When I put the following formula in the Control
Source

=Format([TodaysDate],"yymmdd") & Format([RunNumber]),"000")

it is not showing anything in the control
on the form. When I go back to redo it, the expression
is not in the Control Source.

 

[Steve Schapel]

Tom,

I can't think of any reason for it stopping at 10. The RunNumber field
is set up in the design of the table as a Number data type, right? The
expression you quoted is entered in the Default Value property of the
RunNumber control on your form, right? What is actually happening if
you try to put in another record beyond the one with 10? Error message?
10 is repeated? No number is added? What happens if you close the
form, then open it again, and go to a new record...

The method I gave you should already reset the number to 0 at midnight.

 

[Guest]

Steve,
Thanks! Operator error. Didn't have the
RunNumber field set to Number. Where do
I change the "type" to "data"? It did
change to 001 after midnight when I
checked it this a.m. You've been a BIG
help, Thanks!
Tom

Tom,

I can't think of any reason for it stopping at 10. The RunNumber field
is set up in the design of the table as a Number data type, right? The
expression you quoted is entered in the Default Value property of the
RunNumber control on your form, right? What is actually happening if
you try to put in another record beyond the one with 10? Error message?
10 is repeated? No number is added? What happens if you close the
form, then open it again, and go to a new record...

The method I gave you should already reset the number to 0 at midnight.

--
Steve Schapel, Microsoft Access MVP

Steve,
Got it! Now a little fine tuning.
For some reason, the count is stopping a 010
in the "text box" and 10 in the RunNumber
Max possible should be "999"
Here is the code in the "RunNumber"

Nz(DMax("[RunNumber]","DispatchTable","[TodaysDate]=Date()"),0)+1

Also, is there a way to have this number reset to 001 at Midnight?

Tom

 

[Steve Schapel]

Tom,

Great to hear that we've made good progress!

As regards "where do I change the 'type' to 'data'?", I was referring to
the culumn in design view of the table, with the heading "Data Type" and
it seems you have already changed this to Number.

Let us know if any further help required. Best wishes with the rest of
the project.