Results 1 to 6 of 6
  1. #1
    TINMAN is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    64

    Auto Numbering with Preceding Zeros

    I plan on creating a table in Microsoft Access for capturing service ticket information. I plan on using the Auto Number ID field created by Access for a field I plan on naming Ticket Number. I would like the field for Ticket Number to look like TickNbrXXXXXXX. Where the XXXXXXX portion of the field would be equal to the AutoID number assigned by Access with preceding zeros. For example the first record would look like TickNbr0000001. This would limit my largest Ticket Numbers to TickNbr9999999. What kind of expression could I use to do this and do you think it is a good idea to use the Auto Number ID field in this fashion.

    If this is not a good idea do you have any auto numbering suggestions for incrementing this field by one after each new ticket record is created with preceding zeros.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Autonumber is only guaranteed to be unique, not increasing nor positive although I've never observed otherwise. It also will not prevent gaps in sequence due to canceling data entry. If you must account for every number in sequence then autonumber is not the way to go.

    Whether autonumber or number field, apply formatting to display however you want. It will not change the data.

    "TickNbr" & Format([ID], "0000000")

    Incrementing a custom unique identifier is a common topic. Check out some of the links at bottom of page.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    AutoNumber
    ----------------
    Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key.


    Microsoft Access Tables: Primary Key Tips and Techniques

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Tinman,

    Good info and advice has been provided by June and Steve. But, I'd like to hear more of the rationale for your proposed format. Is it mandatory to have all the numbers in sequence? Preceding zeros indicates you'd be working with text. You might research Dmax +1 for ideas on ensuring numbers in sequence.

  5. #5
    TINMAN is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    64
    Thanks for the formatting tip. Gaps in sequence as I do not plan on accounting for every number in sequence the main thing I want is a unique number. Thanks for your help.

  6. #6
    TINMAN is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    64
    My primary rationale is to create a unique text identifier for a service ticket form (Number to be printed at the top of form or sheet of paper,) this text number is a field (TickNbr) of a service ticket table. This field is then going to be used as a linking field to a customer table and also a vehicle information table. My database skills are pretty limited but this is what I came up to tackle this task of linking.

    I will research Dmax+1 for other ideas on ensuring numbers in sequence.

    Thanks for your help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Auto numbering
    By hemaazez in forum Access
    Replies: 9
    Last Post: 02-18-2019, 11:50 AM
  2. Auto numbering of new records
    By edwardcga in forum Forms
    Replies: 1
    Last Post: 10-20-2013, 04:19 PM
  3. Access Auto Numbering
    By edieb in forum Access
    Replies: 1
    Last Post: 09-25-2013, 11:03 AM
  4. Auto numbering of forms
    By bgeorge12 in forum Forms
    Replies: 5
    Last Post: 06-30-2011, 05:05 PM
  5. Auto Numbering
    By rkruczk in forum Forms
    Replies: 0
    Last Post: 10-09-2006, 04:25 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums