how to select month+year based on current year in VBA

[accessLearn]

I have a form in access where I need to generate employee_Id based on employment_dt in the format month(employment_dt)+Year(Employment_dt)-001 (3 digit number incrementing 1)
I need to select max(monthand year)based on the most recent year but dmax(employment_dt) selects based on max(month)
Ex-
If I have 1214 and 0115, I want to select 0115 and not 1214 but getting 1214.
How can I do that?

Thanks!!

[June7]

Generating custom unique identifier is a common topic.

Basing an employee ID on a date seems poor schema to me. What if an employee is re-hired?

Probably get 1214 because that number is greater than 0115.

If you want records to filter/sort chronologically, put year first: 1501, 1412

[accessLearn]

I do not have the option of putting year first and then month as it is the way it has been setup and ids have been provided to employees.

[June7]

Post the code you are using.

[orange]

What are they using now to generate these numbers? Are you developing new code or what?

You say you need this for a form. Is this how it is stored in the table also?

You may need some code/function to manipulate the data to get what you need.

Sub test1()

    Dim M As Integer, Y As Integer
    Dim forDisplay As String, ForSorting As Integer
    M = Month(Date)
    Y = Year(Date)
    forDisplay = Format(M, "00") & Right(Y, 2)  'keep the leading 0 and use string
    ForSorting = CInt(Right(Y, 2) & Format(M, "00")) 'sort it by year, then month numeric sort
    Debug.Print forDisplay & vbCrLf & ForSorting
End Sub

'Gives following result
'0715
'1507