Creating a Custom Autonumber with year and letters

[HarryScofs]

I need to create two different custom autonumber fields. Both need the first two characters to be the last two character of the current year. For, Example for this year it would be 11_ _ _.

For the first field called project number the next three characters need to be numbers starting with 001. The Second field called bid numbers needs to be two letters starting with AA.

The numbers should flow like this:

11001
11002
12001



and

11AA
11AB
12AA

Thanks
11AB

[June7]

Requires VBA to create and control customized unique ID. I do something similar to create ID like 2011A-0001. Sequential numbering is easy, sequential alpha I have never attempted. This is example from my project:

Dim strLabNum As String
DoCmd.SetWarnings False
'search for aborted lab number and use that record, else if none then create new record
strLabNum = Nz(DLookup("LabNum", "Submit", "IsNull(DateEnter)"), "")
If strLabNum <> "" Then
DoCmd.RunSQL "UPDATE Submit SET DateEnter=#" & Date & "# WHERE LabNum='" & strLabNum & "'"
Else
strLabNum = Nz(DMax("LabNum", "Submit"), "")
If strLabNum = "" Then
'this accommodates very first generated number of blank database
strLabNum = Year(Date) & "A-0001"
Else
'this accommodates change in year
If Left(strLabNum, 4) = CStr(Year(Date)) Then
strLabNum = Left(strLabNum, 6) & Format(Right(strLabNum, 4) + 1, "0000")
Else
strLabNum = Year(Date) & "A-0001"
End If
End If
DoCmd.RunSQL "INSERT INTO Submit(LabNum, DateEnter, EnterWho) VALUES('" & strLabNum & "', #" & Date & "#, '" & Form_Menu.tbxUser & "')"
End If