troubleshooting Question

Autogenerate number in specific format

Avatar of kdeutsch
kdeutschFlag for United States of America asked on
Microsoft AccessMicrosoft Excel
13 Comments2 Solutions2264 ViewsLast Modified:
I originally got a question aswered her that gave me the below code for autogenerating a number in Xcel that in a specific format that I could then pull into Access.  BUt now my question is can I do this same thing in Access using a text field.  That when they add a new job number it autogenerates the new number in the format.  To do this it would also have to look at the previous filed to generate off of that number so as to ensure they are sequential.  Then when the fiscal year comes around it would also have to reset itself so that it starts over agian.  Here is example of format.
07-001  First 2 numbers are year which turns over every OCt 1st, rest is just a sequential number


Sub AccessNr()
Dim A As String
    Nr = Cells(1, 2).Value
    Nr = Nr + 1
    If Nr >= 1000 Then Nr = 1
    Cells(1, 2).Value = Nr
    If Month(Now()) = 10 Or Month(Now()) = 11 Or Month(Now()) = 12 Then
        If Cells(1, 4).Value = 0 Then
            Cells(1, 4).Value = 1
            Cells(1, 2).Value = 1
        End If
        A = Format(year(Now()) - 1999, "00") + "-" + Format(Cells(1, 2).Value, "000")
        A = Format(year(Now()) - 2000, "00") + "-" + Format(Cells(1, 2).Value, "000")
        Cells(1, 4).Value = 0
    End If
    Cells(1, 3).Value = A
End Sub
back to top

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 13 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros