Generate automatic code with Letter year month and serial number with 3 digit (1 Viewer)

Emeline

New member
Local time
Today, 22:42
Joined
Feb 14, 2022
Messages
2
Hello,
I use Access 2016 on a windows 10 laptop.

I am creating an Access table to manage my personal accountancy and, in my invoice table, I would like to fill in the column invoice number with a serial number code that includes the letter "S", the current year (yy), the current month (mm) and then a serial number of 3 digits starting with 001.

So the code looks like Syymm### (S2202001, S2202002, S2202003...)

So far I managed the first part using the following code in the default value filed of the column Invoice Code of my Invoice table:

="S" & Format(Now(),"yymm")

But I cannot figure out how the code to add automatic serial number starting from 001 after yymm.

Any solution?

Thanks in advance for your help,
Cheers,
Emeline
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:42
Joined
Sep 21, 2011
Messages
14,047
Most people I believe would have a settings table that holds the last used number and increment that.
For each yearmonth, the first entry will not exist obviously so use NZ() to set to 1. From then on use Dmax() to get the last and increment, and save.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:42
Joined
May 7, 2009
Messages
19,169
you create a function that will do the serial for you:
Code:
Public Function fncSerial() As String
    Const THE_TABLE As String = "yourTableNameHere"
    Const THE_FIELD As String = "yourSerialFieldHere"
    
    Dim fmt As String
    Dim ret As String
    
    fmt = "S" & Format$(Date, "yymm")
    ret = Nz(DMax(THE_FIELD, THE_TABLE, THE_FIELD & " Like '" & fmt & "*'"), fmt & "000")
    fncSerial = fmt & Format$(Val(Right$(ret, 3)) + 1, "000")
End Function

on your Form:

=fncSerial()
 

jdraw

Super Moderator
Staff member
Local time
Today, 17:42
Joined
Jan 23, 2006
Messages
15,364
Access and relational databases generally work extremely well with atomic data --1 fact 1 field.
Atomic means the data can't be broken down further into multiple values.
Many new to database concoct codification schemes that are unnecessary in most cases. You can concatenate atomic field values as needed.
As for sequential values -with leading 0's - you are dealing with text.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:42
Joined
Feb 19, 2002
Messages
42,976
Here's a sample that generates custom sequence numbers using atomic fields. You can choose to store the generated number for convenience but it is always easier to work with atomic fields as @jdraw pointed out.
 

Attachments

  • CustomSequenceNumber20210303.zip
    93.7 KB · Views: 307

Users who are viewing this thread

Top Bottom