Formatted Auto Numbering

  • Thread starter Thread starter PARANOiA Genesis
  • Start date Start date
P

PARANOiA Genesis

Hello!

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.
 
Try a search on "Custom Autonumber". Try the mvps.org/access 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!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
Back
Top