CODEWITHSUNDEEP
ms-accessformatautonumber
Matt H
Matt H

Reputation: 147

Auto Number Format

I currently have an auto number field populated in the format "\E00000".

After some thought I would like to change the auto number to the date that it is made (today's date) and then an incremented value after that, because there may be multiple records being made a day.

Example - E11072018-01 or - E11/07/2018-01

If this is possible to be created as an auto number please let me know.

Upvotes: 0

Views: 257

Answers (1)

Gustav
Gustav

Reputation: 55906

You can use this fancy expression to obtain the next "number":

= Format(Date(), "\Nddmmyyyy\-") & Format(Nx(DMax("Val(Right([NumberField], 2))", "[SomeTable]", "[NumberField] Like Format(Date(), '\Nddmmyyyy\-??'"), 0), "00")

Of course, replace field and table names with those of yours.

To set value in BeforeInsert event:

Me!NumberField.Value = Format(Date(), "\Nddmmyyyy\-") & Format(Nx(DMax("Val(Right([NumberField], 2))", "[SomeTable]", "[NumberField] Like Format(Date(), '\Nddmmyyyy\-??'"), 0), "00")

Upvotes: 0

Related Questions