Using MS Access/Access Formula

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
Hi Michael,

Many thanks for the question.

You will not be able to this with an Auto Number field, unfortunately.  What you will need to do is to add the numbers by code, checking for the previous largest number for that officer and adding 1.

Do you want it to revert back to 1 for a new year?  I have not included the year in the explanation below, but if you do wish to start again, then let me know.

1. Add a new field to your table to store the case number for the officer (CaseNumber)
2. Create a query, with the following fields Badge Number, CaseNumber
3. Add 'Totals' to the query design grid#  It will be found on your toolbar/ribbon, it looks like a funny back the front 3#  
- This will add a new row in the query design grid called 'Total', there will be 'Group By' in each of the rows, change the CaseNumber field to 'Max' instead#  This query will display the maximum number for each Badge Number
- Close and save the query #qryMaxCaseNumber#
4# Add the new field #CaseNumber# to your form, you can hide it if you wish#
5# Then on the BadgeNumber field, we are going to add a bit of code, that will update the CaseNumber field to the appropriate number#  To do this:
- Right Click on the BadgeNumber field and select 'Properties'
- Click on the 'Event' tab
- Place your cursor next to 'After Update', two buttons will appear, click on the one with the 3 dots#
- Choose 'Code Builder' > OK
- This will open the coding window with the following two lines of code:

Private Sub BadgeNumber_AfterUpdate##

End Sub

- In between these two lines of code add the following code:

'******


Me!CaseNumber = Nz#DLookup#"MaxofCaseNumber", "qryMaxCaseNumber", "BadgeNumber = " & Me!BadgeNumber#, 0# + 1



'******

This code looks up the value in qryMaxCaseNumber for the corresponding badge number that = the badge number you have just entered.  It then adds 1 to this value, thus making it the next sequential value.

The NZ, is used in case there is no corresponding badge number, the max case number is then zero, and a 1 is also added.

I have presumed the BadgeNumber is a number field, if it is a text field let me know.

Then change [ID] in your original code to CaseNumber, and all should be good.

Hope this is of some help.  

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Julie Misson

Expertise

My area of expertise is Microsoft Access 2000-2007. I have been building microsoft databases for the past twelve years. I would be competent in answering questions from novice Access 2000 users to the more advanced including VBA. If I cannot help, I am more than likely be able to point you to websites that can.

Experience

I have a Microsoft Access business where I build database for small businesses. These are usually one off designs, where there is no off the shelf software avaliable to meet the business needs. I also teach Microsft Access to beginners. I am the owner of the www.simply-access.com website.

Education/Credentials
Self taught in Microsoft Access. Have done some units in normalisation and SQL, but most of what I know I have learnt from books originally and more recently the Internet.

©2016 About.com. All rights reserved.