Generating Specialized Auto Numbers

[InspectorJoe]

I am looking to create a tracking number for each record. Ideally, the number would appear as 01-0001. "01" signifing 2001 and "0001" as the first record in 2001. This number would increment by one for each new record (i.e. 01-0002) Finally, this number should automatically reset to 02-0001 on January 1, 2002. Any help would be appreciated.

 

[BNPMike]

If no one else is using the database you can write a query that finds the highest number for the selected year and adds one to it.

If it is multi-user then you risk clashes so usually you would create another 'numbers' table. Step one is to get a number. If you succeed in that then you can set about creating the new entry in your data table(s)knowing no one else will be also using your number.

If all that looks too difficult then don't waste time solving the problem by computer - get the user to enter the new number (you can easily show them the current highest number with a subform). mike.stephens@bnpparibas.com