number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to generate a number automatically, but not use the autonumber
function. I want to keep it in the format I need for the database.
Basically, everytime I do a new record in a form, I want something like this
yy-mm-### where yy is 2 digit year and mm is 2 digit month and # are numbers.
the year and month is pretty easy, but i want to start at 000 and everytime
i do a new record to add one to that 001,002 so on. And then when a new
month comes around to reset to 000. i was trying to use the default value in
the control box, having a little problems there. also noticed messing around
that when you add 1 to 000, the result is 1 not 001, anyway to fix this as
well. any help would be greatly appreciated.
 
Chris said:
I would like to generate a number automatically, but not use the
autonumber function. I want to keep it in the format I need for the
database. Basically, everytime I do a new record in a form, I want
something like this yy-mm-### where yy is 2 digit year and mm is 2
digit month and # are numbers. the year and month is pretty easy, but
i want to start at 000 and everytime i do a new record to add one to
that 001,002 so on. And then when a new month comes around to reset
to 000. i was trying to use the default value in the control box,
having a little problems there. also noticed messing around that
when you add 1 to 000, the result is 1 not 001, anyway to fix this
as well. any help would be greatly appreciated.
Click to expand...

Store this as TWO fields RecordDate and RecordID This makes it MUCH easier to
automatically enter the values and it is a trivial matter to combine the two
fields *for display* in the format you want on forms and reports.

For RecordDate just set the default value property of the field to =Now(). The
RecordID should have no default value and in the form used to enter records you
use the BeforeUpdate event to calculate the ID value and assign it to the
RecordID field...

If IsNull(Me.RecordID) Then
Me.RecordID = Nz(DMax("RecordID", "TableName", "Format(RecordDate, 'yyyymm')
= Format (Date(), 'yyyymm')"), 0) + 1
End If

For display use...

=Format(RecordDate, "yyy-mm-") & Format(RecordID, "000")
 
Thanks for the response rick. i have one additional question to throw a
wrench in it all. how could i say reset the number to 000 each new month.
as you add records you would have yy-mm-### and go from 000 to whatever then
new month would roll around and you could reset the 000. Thanks for your
info.

Chris
 
Chris said:
Thanks for the response rick. i have one additional question to throw a
wrench in it all. how could i say reset the number to 000 each new month.
as you add records you would have yy-mm-### and go from 000 to whatever
then
new month would roll around and you could reset the 000. Thanks for your
info.
Click to expand...

If you examine the code example I gave you'll see that it does start the
numbering over each month. Get it working and then change the date on your
PC and you will see the results.
 
Reconsider using 2 digit years. Were you not around for Y2K? If you are
ever going to use this as a date, it will cause problems.
 
Back
Top