Automatic Number compare with month and year

Do you ever see the autonumber type in microsoft access. this field will increase by itself when we insert the new record in it. but, the increase of number is sequential, we cannot change it into our conditions.

for example, we want the increase of number based on year or month or day or person, and so on. we must create some code to supply what we want.

ok. I will try create some code that will be increase the number based on month and year.

the idea is:
1. if the max month and year is equal with the datenow, then increase one number.
2. if the max month < datenow and max year = datenow, then return it to one.
3. if the datenow - maxyear <> 0, then return it to one.

here code:

Public Sub automatic_number(ByVal ID As String, ByVal dates As Variant)
Dim IDNumber, IDMon, IDYr As String
Dim DDay, DMon, DYr

IDNumber = Right(ID, 4)
IDMon = Mid(ID, 5, 2)
IDYr = Mid(ID, 8, 2)

DDay = Day(dates)
DMon = Month(dates)
DYr = Year(dates)

If (CLng(IDMon) = CLng(DMon)) And (CLng(IDYr) = CLng(Right(DYr, 2))) Then
IDNumber = String(Len(IDNumber) - Len(CStr(CLng(IDNumber))), "0") + CStr(CLng(IDNumber) + 1)
ElseIf (CLng(IDMon) < CLng(DMon)) And (CLng(IDYr) = CLng(Right(DYr, 2))) Then
IDNumber = "0001"
ElseIf (CLng(Right(DYr, 2)) - CLng(IDYr) <> 0) Then
IDNumber = "0001"
Else
Debug.Print "Wrong Comparison"
Exit Sub
End If

NewID = "ABC/" + String(2 - Len(CStr(CLng(DMon))), "0") + CStr(CLng(DMon))
NewID = NewID + "/" + Right(CStr(DYr), 2) + "/" + IDNumber

Debug.Print NewID

End Sub

you can test it with these condition:

Private Sub Form_Load()
Dim MaxID1, MaxID2, MaxID3, MaxID4 As String
Dim Date1, Date2, Date3, Date4

MaxID1 = "ABC/02/08/0003"
MaxID2 = "ABC/03/08/1234"
MaxID3 = "ABC/04/08/0897"
MaxID4 = "ABC/12/08/0778"

Date1 = DateSerial(2008, 2, 4)
Date2 = DateSerial(2008, 3, 15)
Date3 = DateSerial(2008, 5, 1)
Date4 = DateSerial(2009, 1, 1)

automatic_number MaxID1, Date1 --> ABC/02/08/0004
automatic_number MaxID2, Date2 --> ABC/03/08/1235
automatic_number MaxID3, Date3 --> ABC/05/08/0001
automatic_number MaxID4, Date4 --> ABC/01/09/0001
automatic_number MaxID3, Date1 --> Wrong Comparison
automatic_number MaxID2, Date3 --> ABC/05/08/0001

End Sub