troubleshooting Question

Creating an Autonumber with a Prefix for different categories while including the Year and Month

Avatar of masterimi
masterimi asked on
Microsoft Access
5 Comments1 Solution1380 ViewsLast Modified:
I've found possible solutions for problems similar to mine, but not enough to get me where I need to go. What I am looking for is an Incrementing Autonumber including the Month, Year, and a Letter prefix at the beginning of the Autonumber based on the users choice.

For example: A06-05-001, A06-05-002....A06-05-018, A06-05-019
          B06-05-001, B06-05-002....B06-05-025, B06-05-026
          C06-05-001, C06-05-002....C06-05-056, C06-05-057

"A" = Apples (Category), "B" Bananas (Category), "C" Cherries (Category)
"06" = Current year
"05" = Current month
"001" = Counter incrementing for each category

I want to be able to have the user choose a category from a drop down list in a form so it can apply a prefix to the Autonumber including the year and month. I will need to have each category have its own increment so that if a user chooses category "C", the counter will automatically check the last number used for Category "C" and not the others, and apply the next sequential number. The tricky part for this is I am trying to have the counters reset every month for all categories.

I don’t mind using VBA if it will accomplish this task. Hope this makes sense. Thanks in advance for your help.

Join our community to see this answer!
Unlock 1 Answer and 5 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 1 Answer and 5 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