Make Refference

Public Function MakeRef(dat_Date As Date) As String
Dim DB As DAO.Database
Set DB = CurrentDb
Dim RS As DAO.Recordset
Set RS = DB.OpenRecordset("SELECT * FROM Tab_DateRefs ORDER BY Data_Ref;")
Dim strRef As String
Dim intMon As Integer
Dim strMon As String
Dim strSQL As String
Dim strTemp As String

strRef = Right(Year(dat_Date), 2)

intMon = Month(dat_Date)
strMon = Chr(64 + intMon) 'ASCII 65 = A

strRef = strRef & strMon

strSQL = "Data_Ref LIKE '" & strRef & "*'" 'Tab_DateRefs is my Table Data_Ref is where the refferences are stored
RS.MoveFirst
RS.FindLast strSQL

If RS.NoMatch Then 'If your DB is empty or a new month begins the RS will not find anything
    MakeRef = strRef & "01"
Else
    If CInt(Right(RS!Data_Ref, 2) + 1) < 10 Then
        MakeRef = Left(RS!Data_Ref, 4) & CStr(CInt(Right(RS!Data_Ref, 2) + 1)) ' 4 = with the leading zero for 01-09
    Else
        MakeRef = Left(RS!Data_Ref, 3) & CStr(CInt(Right(RS!Data_Ref, 2) + 1)) ' 3 = without the leading zero for 10 to 99
    End If
'Alternativ or for more then 2 digits
        'If RS.NoMatch Then
        '    MakeRef = strRef & "01" 'Use "001" for 3 Digits
        'Else
        '    MakeRef = Left(RS!Data_Ref, 3) & Format(CStr(CInt(Right(RS!Data_Ref, 2) + 1)), "00") 'Use "000" for 3 Digits
        'End If
End If

RS.Close
DB.Close
Set DB = Nothing
Set RS = Nothing
End Function