Perhaps you may be wondering, whey we need something different when there is an Autonumber feature already built-in into Microsoft Access?  Well, the built-in feature may not be suitable for all situations, like Patient’s Unique Registration Numbers in hospitals, with change of date and sequence numbers. We have created a function earlier for generating auto-numbers with date and sequence numbers and you can have that from the first link given below. You may visit the other two links for some different approaches for generating Auto-numbers in Queries too.

We are now going to take a different approach in generating auto-numbers with date and sequence numbers. Let us take a re-look at the last method we have created with date & sequence numbers, and how we are going to reformat the same thing in the new method with lesser number of digits as follows:

Sample Dates: 30-10-2012 and 31-10-2012

The Auto-number generated for patient’s registration looks like the following, the format used in the earlier article (first link above):

Format: yyyymmdd-999

Autonumber-with-Date method-1
Saved Number Display with Inputmask
20121030001 20121030-001
20121030002 20121030-002
20121030003 20121030-003
20121030004 20121030-004
20121030005 20121030-005
20121031001 20121031-001
20121031002 20121031-002
20121031003 20121031-003

The dash in the number is inserted using the Input-mask for better readability in the display control. In the above example it uses eight digits for displaying the date part and three digits for serial numbers. This method requires a total of 11 digits for the auto-number.

In the following new method, we are going to create date-wise changing auto-numbers, which takes only eight digits, like the example shown below:

Sample Date: 30-10-2012 and 31-10-2012

New display format: yyddd-999

First two digits (yy) stores the year (12), next three digits (ddd) are the day number from 1st January (is 001) onwards, October 30th, 2012 is 304th day from 1st January 2012.

Autonumber-with-Date method-2
Saved Number Display with Inputmask
12304001 12304-001
12304002 12304-002
12304003 12304-003
12304004 12304-004
12304005 12304-005
12305001 12305-001
12305002 12305-002
12305003 12305-003

The sequence numbers resets to 001 when the date changes.  The new method result is somewhat compact in size and takes only eight digits to store the auto-number in place of eleven digits in the earlier method.

Copy and paste the following VBA Code into a Standard Module of your Database:

Public Function DaysAsOnMonth(ByVal dt As Date) As Long
Dim i As Integer, j As Integer, tdays As Long, d As Long

On Error GoTo DaysAsOnMonth_Err
i = Month(dt)
d = DatePart("d", dt)

For j = 1 To i - 1
tdays = tdays + Choose(j, 31, 28 + IIf(Year(dt) / 4 = Int(Year(dt) / 4), 1, 0), 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)
Next
If (Year(dt) Mod 400) = 0 And Month(dt) > 2 Then
  tdays = tdays - 1
End If
tdays = Val(Right(Year(dt), 2)) * 10 ^ 3 + tdays
tdays = tdays + d
DaysAsOnMonth = tdays

DaysAsOnMonth_Exit:
Exit Function

DaysAsOnMonth_Err:
MsgBox Err & " : " & Err.Description, , "DaysAsOnMonth()"
Resume DaysAsOnMonth_Exit


End Function

Public Function AutoNumber(ByVal strField As String, ByVal strTable As String) As String
Dim dmval As String, dt1 As String, dt2 As String, Seq As Integer, dv As String

On Error GoTo AutoNumber_Err

'get the highest existing value from the table
dmval = Nz(DMax(strField, strTable), 0)

'if returned value is 0 then the table is new and empty
'create autonumber with current date and sequence 001
If Val(dmval) = 0 Then
   dv = Format(DaysAsOnMonth(Date) * 10 ^ 3 + 1)
   AutoNumber = dv
   Exit Function
End If

'format the number as an 11 digit number
dv = Format(dmval, "00000000")
'take the 3 digit sequence number separately
Seq = Val(Right(dv, 3))
'take the date value separately
dt1 = Left(dv, 5)
'get today's date
dt2 = Format(DaysAsOnMonth(Date))
'compare the latest date taken from the table
'with today's date
If dt1 = dt2 Then 'if both dates are same
   Seq = Seq + 1 'increment the sequence number
   'add the sequence number to the date and return
   AutoNumber = Format(Val(dt1) * 10 ^ 3 + Seq)
   Exit Function
Else 'the dates are different
   'take today's date and start the sequence with 1
   AutoNumber = Format(Val(dt2) * 10 ^ 3 + 1)
End If

AutoNumber_Exit:
Exit Function

AutoNumber_Err:
MsgBox Err & " : " & Err.Description, , "AutoNumber()"
Resume AutoNumber_Exit

End Function

The first function DaysOfMonth() is called from the AutoNumber() Function to calculate numbers days from January 1st to the date passed as parameter to the function.  The input date 30-10-2012 will return the result value 304, i.e. 31+29+31+30+31+30+31+31+30+30 = 304.

The trial run procedure for the new method is already published in an earlier Article. I will take you there to the exact point in that Article, from where you can continue reading and prepare yourself for the demo. All that you should do is to change the Function name Autonum(), appearing in those sample run lines, to AutoNumber().

Click to continue…