Results 1 to 3 of 3
  1. #1
    kanders
    Guest

    Automated Incrementing Field

    At the end of last year I got some help with creating an automated incrementing field. It was working great until it became 2001. The field is supposed to show the letter "E" and then two digits for the year (01) and then the day of the year in three digits, for example today would be 054 because it's the 54th day of the year, and then the number of the entry using three digits. This is the code I'm using:
    Dim sPrefix As String
    sPrefix = "E" & Format(Date, "yyy")
    [RefNmbr] = sPrefix & Format(Val(Mid(Nz(DMax("[RefNmbr]", "[tblRequests]", "Left([RefNmbr],6)='" & sPrefix & "'"), 0), 7)) + 1, "000")

    It was working fine when the number of the day of the year was three digits, like 256. But now that the year is less than 100 days old it's not working. For example, the first one for today reads E0154001 which won't work for incrementing because the day of the year is only showing 2 digits.
    Any suggestions?
    Thanks.

  2. #2
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Massachusetts
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automated Incrementing Field

    Try this for the sPrefix Line:

    <pre>sPrefix = "E" & Format(Date, "yy") & Format(DatePart("y", Date), "000")</pre>


  3. #3
    kanders
    Guest

    Re: Automated Incrementing Field

    Thank you so much! It seems to be working now.

Posting Permissions

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