Automatically generate Date + 4-digit sequence num

Question

I need to automatically generate a 12 character value for my Business Key. Without any user interaction.

8 character -> Today Date        (yyyymmdd or ddmmyyyy).
+
4 character -> Sequential Number (0001,0002,0003).

The Sequential Number must reset on each new day.

Is it possible to do this in Microsoft Access 2010+ without any coding involved?

Answer 1

Good question, thanks for the challenge!

After some search, it seems it's possible to do that.

You can prefix the AutoNuber value by processing like the explanation available here: http://www.databasedev.co.uk/add_prefix.html

You can try to specify in the format of the field a format(now(),"ddmmyyyy").

Check this page for more informations, another user seems to have the same problem and got a solution: http://bytes.com/topic/access/answers/695188-custom-made-autonumber-show-todays-date

Hope it's helping you!

Answer 2

Since you are using Access 2010+ the best way to accomplish your goal would be to use a Before Change data macro like this

To create the Before Change macro, click the "Before Change" button on the "Table" tab of the ribbon when the table is open in Datasheet View:

For more details on Data Macros see

Create a data macro