Results 1 to 2 of 2
  1. #1
    New Lounger
    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...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 30 Times in 30 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)
    End If

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts