Using MS Access/Odd Squential Numbering

Advertisement


Question
I have a box in which I am combining three boxes into one to complete a sequential number.  I am combining the year with the officer's badge number and a sequential number.  I am putting the below listed code into a box to create this number:
=Format([Eval Date],"yy") & "-" & [Badge Number] & "-" & Format([ID],"0000")  

So it auto populates for example 13-514-0001.  This is the year, the officer's badge number and then the sequential number.  This actually works but my problem is that when I add another officer's records then the sequential numbering jumps and I need them to be in order based on badge number.  So I need the numbers to be in sequential order by officer.  So if there are two officers or more that are tracking their individual cases I don't want the "ID" number to jump since it was an autonumber.  So for each case for each officer the last number should be in sequential order.  right now I enter a case for officer badge number 514 and its number is 0001, but then when I enter for another officer, the last number jumps to 0002, and then if i enter another case for officer 514, it jumps to 0003, instead of being 0002 for being the second case for officer 514.

Answer
Don't use an Autonumber. You'll have to increment your own custom counter field. See this tutorial for instructions: Microsoft Access DMax Counter


Be sure to visit my web site and watch my FREE 2-hour-long Microsoft Access Video Tutorial

Cordially,
Richard Rost
599CD Computer Training

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Richard Rost

Expertise

I am happy to answer any kinds of questions about Microsoft Access - from basic table design to advanced VBA programming. Also, please feel free to check the Access Tips & Tricks section of my web site, and free Access 101 tutorial.

Experience

I am the president of AccessLearningZone.com and 599CD.com. I specialize in Microsoft Access Tutorials. I have been teaching Access in the classroom since 1994, and online since 2002.

Education/Credentials
I am a self-taught Access expert. I have been building databases for clients since the early 90s. You can see a sample of my Access Tutorials on my web site at 599CD.com

©2016 About.com. All rights reserved.