Results 1 to 2 of 2
Thread: Autonumber of sorts (2003)
2004-06-09, 19:31 #1
- Join Date
- Jun 2004
- Thanked 0 Times in 0 Posts
Autonumber of sorts (2003)
Is there a way that I can make a counting number using the following format:
2 digit year followed by a hyphen then numbers increasing from 100 (e.g. 04-100, next number would be 04-101)
After the year turns to 2005 the numbers would start over at 05-100 then 05-101 and so forth.
All of this in one field also if possible...
2004-06-09, 19:51 #2
- Join Date
- Mar 2002
- Thanked 28 Times in 28 Posts
Re: Autonumber of sorts (2003)
You must do this in a form. So you should not let the user edit data in a table or query directly, but that is always wise anyway. If only one user will be adding new records at any given time, you can use a form based on the table, and calculate the new value in the Before Insert event of the form. If multiple users may be adding new records at the same time, this can lead to conflicts. In that case, use a separate unbound form for entering new records, and only calculate the new value when the user has filled in the necessary values and clicks OK to save the data to a new record.
The code to calculate the new value is the same in both cases. In the following, PAN (for PseudoAutoNumber) is the name of the field, and tblPAN the name of the table
Dim varLastPAN As Variant
Dim strNewPAN As String
varLastPAN = DMax("PAN", "tblPAN", "PAN Like '" & Format(Date, "yy") & "*'")
If IsNull(varLastPAN) Then
strNewPAN = Format(Date, "yy") & "-100"
strNewPAN = Format(Date, "yy") & "-" & (Val(Right(varLastPAN, 3)) + 1)