Records - Automatic Numbering

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

Guest

Hello,

Our main table uses automatic numbering as its primary key. We also use
this same field in our main form as our "job number".

On Jan. 1st, we'd like to restart the numbering back to 1. E.g., 2006-1,
2006-2, etc. Can this be done? Alternatively, if it's not possible to add
the year, we'd just like to restart the numbering back to 1.

How can we do this?

Thanks!
Rosemary
 
Hi,
you can create a new text field for job number, fill 2005 year values using
update query, and create a function to generate new job number. Old field
you can leave as primary key, just hide it in user interface
 
Autonumber fields are just that and cannot be reset to 1 EXCEPT if you
delete all the records in the table and then compact the database.

If you need the job number to be one up by year, I would suggest you add two
new fields. One field to contain the year number (YearNo)and another to
contain the your one up number (JobNo).

The default value of the YearNo field would be set to "=Year(Date())" - no
quotes.
Your one up value would have to be calculated, but that could be done with a
formula and some vba code in the entry form.

The formula for the next JobNo for any YearNo could be as simple as:
NZ(Dmax("JobNo","YourMainTable","YearNo =" & Chr(34) & Year(Date()) &
Chr(34)),0)+1

If YearNo is not a text field, but is a number field then drop the & Chr(34)
 
Back
Top