You are here:

Using MS Access/Sequential Numbering Based on Criteria


Hi Scott,
I was asking about sequential numbering last year, but I have not had time since then implement it as my job has changed somewhat.  Anyway, here I am now back at it again.  You did send me a link, but I think my request may be a little complicated.  So this is the situation..  There are two separate sections in our department, one section is identified as P5555 and the other section is identified by P6666.  It would be great if the two sections could use the same database to record all purchase orders.  So what I want to do is have the sequential number based on the identity of each section and also the fiscal year.  For example, this year, section P5555 records its purchases as P5555-140001, with the next purchase order being P5555-140002 and so on.  At the same time, P6666 would record their purchases as P6666-140001 followed by the next purchase which would be P6666-140002 and so on.  The number 14 on all purchase orders would indicate the current fiscal year, 2014.  So I was thinking that the user would go into the database, open a form to enter the new purchase order.  On the form, there would be a drop down menu to choose the section ID, then another drop down menu to choose the fiscal year (which would probably be written as 2014-2015 for 14/15, or something like that), then once the criteria has been chosen, the user would tab out of the last drop- down to the Purchase Order ID field and the field would automatically populate with the next number for that section for that year.  My apologies if this description sounds completely confusing.  I just want to ensure that I donít leave anything out.  Thanks so much for all of your help, I really appreciate it very much.  Cheers, Carla  

Not confusing at all. My blog article on Sequential Numbering covers specifically how to do that. Its a very common usage for Sequential Numbering.

What you need to understand (as the blog states), is that you should NOT be storing this the entire number in a single field, but since the number is based on three values, all three should be stored separately. The method described in my blog uses the Dmax function. You will need to set the criteria for the DMax to test for both the Section AND Fiscal Year.

Otherwise you have the process correctly.

Review, the blog article and, if you have any more questions feel free to ask.  

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2017 All rights reserved.