A common question I’ve received has to do with sequential
numbering. People often want to use some identifier for their records that
involves information about the record and a sequential number. Sometimes these
numbers are sequential throughout the table, other times the sequence is
specific to other criteria.
To illustrate how to do this, I’m going to use the following
Co. needs to sequentially number their Purchase Orders to conform to standard
Inc. wants to sequentially number inquiries they receive but within each year.
& Vine, a law firm, wants to tag all documents they receive with a client
ID, case number and then number them sequentially by case.
There are three issues common to all three scenarios. First,
the sequential number is stored separately in the table as an Integer datatype
(might need Long Integer). Second, the DMax function is used to determine the
last number used. Third, the number needs to be generated immediately prior to
saving the record.
One other caveat here; the number generated in these
scenarios is NOT a primary key. These tables should use an Autonumber as the
PK. The possible exception would be scenario 1, but I would still tend to use
an Autonumber as the PK.
We’ll start with the simplest, scenario 1. Acme will use a
straight sequential number for its Purchase Order numbers. Add a (Long?)
Integer field to the table named PONum.
Add a control to your Purchase Order form bound to that field. Then use
the following expression to populate that control:
The Nz function will return 0 if there are no records so
this expression will return a 1 the first time and then increment the highest
number each additional time. The key is where to put this line of code. As I
said it needs to be generated immediately prior to saving the record so that
another user does not get the same number. So generally I would put this behind
a Save button or a button to specifically generate the number. I would then add
a second line to commit the records. Either:
Next we will look at scenario 2. The variation here is that
Apex wants to start each year with anew sequence of numbers. Also, they want to
include the year when displaying this number. We still use a DMax, but we need
to add criteria to determine the year. So Apex needs to have a field in their
table that indicates the date. Generally, such inquiries will have a date/time
stamp to indicate when received or the user will input the date and time. So we
will assume there is a field in record called InquiryDate. We will also add an
Integer field named Sequence. On the form we will add a control bound to
Sequence, but set its Visible property to No. The line of code will look like
= Nz(DMax(“[Sequence]”,”tblInquiry”,”Year([InquiryDate]) = “ &
With this expression we check only for records that are in
the same year as the Inquiry date to increment the sequence. Again, the NZ
function will automatically restart the sequence with each year. Cleary,
though, we cannot generate this number until the InquiryDate has been entered.
Is this value is a user entry, then you may want to check that it’s filled in
before generating the number. Something like:
MsgBox “Inquiry Date must be entered
= Nz(DMax(“[Sequence]”,”tblInquiry”,”Year([InquiryDate]) = “ &
Also again, you want to generate this number immediately
before saving the record.
The final piece is to display the identifier. For that you
use the following expression:
& “-“ &Format([Sequence],”0000”)
This expression can be used anywhere you want to display
this identifier. It will appear like this: 2009-0010,
indicating the tenth inquiry of 2009. You can use that as the Controlsource of
a control on a form or report. In a query you would preface with an Alias
rather than the equals sign (i.e.; Inquiry Code:).
The final scenario is very similar to Scenario 2. The two
main differences are in the criteria of the DMax and the expression to display
the identifier. The document table already has two fields to identify a
document; ClientCode, which is a 5 character code that identifies the client
and CaseNumber, which is an alpha-numeric code assigned to the case. Since the
Case Number is unique to the Client, we only need to set the criteria in the
DMax for the case number. We will also need to add an Integer field named
Sequence to the table. So the Dmax expression will look like this:
= Nz(DMax(“[Sequence]”,”tblDocument”,” [CaseNumber] = ‘“ & Me.[txtCaseNumber]
Notice here that we need to surround CaseNumber with Single
quotes since it’s a Text datatype. As in the other scenarios, The NZ function
automatically restarts the sequence when a new CaseNumber is used. In this
scenario we would want to check whether the CaseNumber has been entered before
generating the sequence.
For displaying this identifier, we would use an expression
& “/“ & [CaseNumber] & “-“
In this scenario we are assuming that there will be no more
than 999 documents attached to a case. In Scenario 2 we assumed no more than
9999 inquires during a year. So you need to adjust the number of zeros when
formatting Sequence for the anticipated number of records. Of course this can
always be changed later. You also don’t need to format the sequence with
leading zeros as the Format function does. As shown the expression returns
something like: DCASD/CI123-025 for
the 25th document in case CI123 for client DCASD. Without leading
zeros it would be: DCASD/CI123-25. The
advantage to the latter is that you don’t have to anticipate the number of
records you might have in the sequence, but I’ve found many users prefer a more
uniform number with the leading zeros.
As you’ve seen, the identifier is built using other data
besides the sequence number. Since this other data is a part of the record, you
don’t want to store this identifier since that means storing the same data
twice. That’s why we use an expression to display the identifier rather than
To recap, you use a DMax function to return the highest
number in the Sequence and increment it by 1. You assign the incremented number
to a control on your form and then immediately save the record. If your
identifier includes additional information from the record, you use an expression
that concatenates that information with the sequence number to display the full
With these techniques, you can generate a sequential number
for just about any situation. I hope you find this information helpful.
© 2009 Scott B. Diamond — Diamond Computing Associates