Formatted Auto Numbering


PARANOiA Genesis


My company uses Access to track trouble tickets. We have a specific format
for numbering our jobs as follows:

NGJJJJJXXX where NG is just those letters, JJJJJ is the Julian Date (eg.
08172), and XXX is the number of the job on that Julian Day (the first job of
08172 is 001, then 002, and we want it to reset to 001 each day). So an
example of our first job number for today would be "NG08172001." We would
like this field to populate itself when a user opens the form to create a new
job. There is a potential for multiple users to access the database at the
same time, but not likely.

What kind of code would we need to use in order to generate this kind of
number on our database? We've been spending months tearing through every
Access database book and website and can't for the life of us figure this
out. We have very limited experience with code so it's been kind of tough.
Any help with this would be greatly appreciated. Thanks.



Jeff Boyce

Try a search on "Custom Autonumber". Try the website.

You'll be building a function that "gets" your next (sequence) number.

Note: If every "trouble ticket" number starts with "NG", don't store that!
Use formatting (or concatenation in a query) to add that in front.

Note2: If you (already) are storing the date/time of the ticket, don't
store the JJJJJ -- you can derive (i.e., calculate) that from the date stamp
on the record.

Note3: The only part you really need to store is the sequence number, and
then, only the integer value (i.e., "7", not "007"). You can use the
Format() function to make it look like "007".

In the end, you can use a query to "build" your NGJJJJJ### ticket number.

Good luck!


Jeff Boyce

Microsoft Office/Access MVP

Microsoft IT Academy Program Mentor

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question