Code combination in microsoft access (yyxxxx format)

Question

I'm struggeling with a part of code that I want to implement in Microsoft Access.

The required code is used for project asignments. The code format contains the last 2 numbers of the year + 4 digits which add up until a new year, then the last 2 numbers of the year add up with 1 and the 4 digits start at 1 again. For example:

2019: 190001 = first task; 190002 = second task; etc...

2020: 200001 = first task; 200002 = second task; etc...

Could anybody help me out how to code this in Microsoft Access, preferably by VBA? This way I can asign the code to a "submit" button to avoid similar numbers.

Thanks!

Answer 1

Formatting your code given an integer could be achieved in several ways, here is one possible method:

Function ProjectCode(ByVal n As Long) As Long
    ProjectCode = CLng(Format(Date, "yy") & Format(n, "0000"))
End Function

?ProjectCode(1)
 200001 
?ProjectCode(2)
 200002 
?ProjectCode(100)
 200100