Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    autonumbering (Access03)

    A while back I had some assistance with the following code.

    If txtClassification = "Manager" Then
    OldID = Nz(DMax("Right(LearnerID,4)", "tblLearners", "left(LearnerID,1)='M'"), "0000")
    NewID = Format("M-") & Format(Right(OldID, 4) + 1, "0000")
    Me.LearnerID = NewID
    ElseIf txtClassification = "Agency" Then
    OldID = Nz(DMax("Right(LearnerID,4)", "tblLearners", "left(LearnerID,1)='A'"), "0000")
    NewID = Format("A-") & Format(Right(OldID, 4) + 1, "0000")
    Me.LearnerID = NewID
    End If

    Due to a need for a change I need to revise the code. The staff have numbers from 0 to 586. I need have the numbers go from the 586. I have the code below.

    If txtClassification = "Staff" Then
    OldID = Nz(DMax("Right(LearnerID,4)", "tblLearners", "left(LearnerID,1)='0'"), "0000")
    NewID = Format(Right(OldID, 4) + 1, "0000")
    Me.LearnerID = NewID

    This gave me 0001. How do I revise the code to read the current number, add 1, and not restrict to four places? I don't want to get 001, 002, etc.

    Thank you.

    Fay

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: autonumbering (Access03)

    Do you want numbers with 3 places now? What if they go above 999?
    And do I understand correctly that staff don't have a letter before the number?

    Perhaps this will do what you want. If not, please provide more information.

    ElseIf txtClassification = "Agency" Then
    OldID = Nz(DMax("Right(LearnerID,4)", "tblLearners", "left(LearnerID,1)='A'"), "0000")
    NewID = Format("A-") & Format(Right(OldID, 4) + 1, "0000")
    Me.LearnerID = NewID
    End If

    Due to a need for a change I need to revise the code. The staff have numbers from 0 to 586. I need have the numbers go from the 586. I have the code below.

    If txtClassification = "Staff" Then
    OldID = Nz(DMax("Right(LearnerID,3)", "tblLearners", "Left(LearnerID,1)='0'"), "586")
    NewID = Format(Right(OldID, 3) + 1, "000")
    Me.LearnerID = NewID
    End If

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: autonumbering (Access03)

    The numbers may go beyond 999 in the future and there is no letter before the number. I tried your code and got 020. There is a 20 in the database. Technically I guess there is no real reason why 001 or 002 numbers couldn't be used, I guess. So the major issue is allowing up to four digits.

    Thanks Hans.

    Fay

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: autonumbering (Access03)

    Modify the code as follow :
    If txtClassification = "Staff" Then
    OldID = Nz(DMax("Right(LearnerID,4)", "tblLearners", "Left(LearnerID,1)<>'A' And Left(LearnerID,1)<>'M' "), "0586")
    If Val(OldID) < 586 Then
    OldID = "0586"
    End If
    NewID = Format(Right(OldID, 4) + 1, "0000")
    Me.LearnerID = NewID
    End If
    Francois

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: autonumbering (Access03)

    Hi Francois your code worked. Is there anyway to have just three digits until we reach 999 and have it roll over to 1000?

    Sorry it didn't work after all. With the second new learner it still gives me 0587. So I went back to Hans code, but the issue still remains about the three to four digits.

    Thanks Fay

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: autonumbering (Access03)

    Modify the code as follow :
    If txtClassification = "Staff" Then
    OldID = Nz(DMax("Right(LearnerID,4)", "tblLearners", "Left(LearnerID,1)<>'A' And Left(LearnerID,1)<>'M' "), "0586")
    If Val(OldID) < 586 Then
    OldID = "0586"
    End If
    If Val(OldId) < 1000 Then
    NewID = Format(Right(OldID, 3) + 1, "000")
    Else
    NewID = Format(Right(OldID, 4) + 1, "0000")
    End If
    Me.LearnerID = NewID
    End If
    But should you do this ?
    Remember that LearnerID is a string. Removing the 0 before numbers smaller then 1000 will not give the right order if you sort on it. 1000 will be sorted before 586 because 1 is smaller than 5.
    Francois

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: autonumbering (Access03)

    Second thought, you could also use :
    If Val(OldId) < 1000 Then
    NewID = Format(Right(OldID, 3) + 1, " 000")
    Else
    NewID = Format(Right(OldID, 4) + 1, "0000")
    End If

    I add a space before the 000 in the first format line.
    This will give you a correct sort order.
    Up to you to decide what you want to use.
    Francois

  8. #8
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: autonumbering (Access03)

    I cured the sequencing issue by modifying all of my numbers to run from 001 - 586. I built it the If Else for <1000 or >1000. I think I can now more to other issues. Thank you both.

Posting Permissions

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