3

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?

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Joshua Dalley
  • 339
  • 3
  • 6
  • 23
  • You would have to store that sequential number somewhere and the current today's date. Are you looking to store it in a table and then run sql queries to generate the 10 character values as well as update the stored last value in the table? – Linger Jun 19 '14 at 17:48

2 Answers2

3

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

DataMacro.png

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:

BeforeChange.png

For more details on Data Macros see

Create a data macro

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
0

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!

Nicolas Henrard
  • 843
  • 8
  • 19
  • I downvoted this answer. See [Your answer is in another castle](http://meta.stackexchange.com/q/225370/275530) for an explanation why. – CBRF23 Jan 09 '16 at 21:37