Search code examples
ms-accessms-access-2013ms-access-2016

Code combination in microsoft access (yyxxxx format)


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!


Solution

  • 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