You are here:

Using MS Access/Assigning Sequential Numbering with Project Code


Hello Richard. I am a beginner and have been using your awesome tutorials to learn Access. I am trying to build a multi-user spreadsheet for our office to use as a project log. For each project, I want it to create a project number sequentially and automatically in the format WP15-001 where the 001 would be 002 for the next project and so on. I know I should not use auto number, so I'm trying the DMax function. I've created a macro on "After Insert" and have it to "Edit Record" and "Set Field". Right now I have Nz(DMax("[ProjectNumberInt]","ProjectListT"),[0])+1, but it keeps returning a 0 null value and not adding. I was going to then have another ProjectNumber data set to then display the full WP15-001 value. Please help!

You don't need an event for that, just put the DMAX in the Default Value property for that field in Form Design View.


As long as you seed the table with at least one record, you won't have to worry about using NZ.

Don't put your 0 inside square brackets. That refers to a FIELD called 0. Since you don't have any spaces in your field names or table names, you don't have to worry about using brackets.

Hope this helps.

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

Richard Rost
Microsoft Access MVP
Computer Learning Zone  

Using MS Access

All Answers

Answers by Expert:

Ask Experts


Richard Rost


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.


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

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

©2017 All rights reserved.