Using MS Access/Odd Squential Numbering
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.
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
599CD Computer Training