Introduction.
Perhaps you may be wondering, why 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 the 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 related links for some different approaches for generating Auto-numbers in Queries too.
- Autonumber with Date and Sequence Number
- Product Sequence with Auto numbers.
- Autonumbering in Query Column.
We are now going to take a different approach to generate auto-numbers with the 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 a lesser number of digits as follows:
Sample Data Image.
Sample Dates: 30-10-2012 and 31-10-2012
The Auto-number generated for patient registration looks like the following, the format used in the earlier article (first link above):
Format: yyyymmdd-999
| Saved Number | Display with Input mask |
|---|---|
| 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
The first two digits (yy) stores the year (12), the next three digits (ddd) are the day number from 1st January (is 001) onwards, October 30th, 2012 is the 304th day from 1st January 2012.
| Saved Number | Display with Input mask |
|---|---|
| 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 reset 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.
DaysAsOnMonth() Function.
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
The AutoNumber() 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
How It works.
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...
DateAndSeq.zip
