Auto incrementing

[Cynthia]

I am trying to design a database that will automatically enter a unique ID for a field that would look like 07-001, (the first 2 digits are the year, a dash, followed by a 3 digit number that auto increases from Jan 1 till Dec 31), then resetting to 08-001 at the beginning of the next year etc.

Does this even make sense?

[willakawill]

Hi Cynthia. This is not too difficult. You have to do this yourself in code when you want to add another record to the database. Create the value as you have indicated and store it in the uniqueID field.

[AccessIdiot]

An alternative (although maybe cumbersome) way I've done in SQL uses a table to hold values that auto increment and then concatenate them into a final field that you would then use.

The year I supposed you could pull off the computer clock and then snip it, or change the value in the table yourself since it only changes once a year. The next number could be auto incremented and then concatenated, making "1" into "001" etc.

hope this helps!

[NeoPa]

You could append after the Year value, another value found by adding 1 to a DMax() value of the existing records in the table.