Results 1 to 4 of 4
  1. #1
    Hugo Fox is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    1

    Question How do I get MS Access to do an autonumber with a specific format?

    Hi - I am not very computer savy so any help would be greatly appreciated.



    I am looking at doing a database to track development approval applications (I am a town planner).

    From what I can see Access does an auto number function i.e 1 for first record, then 2 and so on.

    I would like to work out how to program Access so it will auto number but using our format for applications.

    i.e:

    1st record: P20-001
    2nd record: P20-002
    3rd record: P20-003

    (20 referencing the year and then obviously an application number. Could it be programmed so it will tick over to 21 next year and so on?).

    Thank you so much in advance for any help!

    Cheers

    Hugo

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    It is never a good idea to create an autonumber type field which has meaning - autonumbers have only one purpose - to uniquely identify a record and to do that it's value is otherwise meaningless. Your requirement has meaning in that it identifies a year and a counter. Better to create a unique field as 'projectID' or similar. You will need to forget most of the principles you would apply in Excel, databases do not work on the same basis. It is also not a good idea to have a field store multiple 'values' - year and a counter - better to keep in separate fields and concatenate when required.

    Better still, just store the year value and concatenate with the autonumber when required. But assuming you want your year counter

    tblApprovals
    ApprovalPK autonumber indexed, no duplicates, primary key - links to foreign keys in other tables)
    ApprovalYear number (indexed, duplicates, OK)*
    ApprovalCount number (indexed, duplicates OK)*
    ApprovalDesc text
    etc

    *also create an multifield index on these two fields, indexed no duplicates

    To display, just concanate the values together - "P" & ApprovalYear & "-" & format(ApprovalCount,"000")

    To create the approvalYear - depends on whether that is 'set' at the time the record is created, but assuming it is, set the default value to year(date)-2000
    To create the approvalCount value use what is referred to as 'dmax+1'
    ApprovalCount=dmax("ApprovalCount","tblApprovals", "ApprovalYear=year(date)-2000)")+1

    To put into one field means you need to break that field down into it's component parts before you can start to determine the correct value

    you would have to find the record with the right year - so starts with P??

    then you need to get the last three characters, and then you need to add 1 - and since the last 3 characters are text, you need to convert it to a number before doing that

    then you need to put it all back together again

    Some words of caution
    - what will you do if in later years you have more that 999 approvals? May not matter but '0001' will sort before '999'
    - if this is going to be a multi user system, you need some basis for ensuring that two users are not creating new records at the same time - otherwise there is a risk that they will create the same new number. All handlable with vba but a lot more work.
    Last edited by CJ_London; 02-21-2020 at 06:00 AM.

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    In case you for some reason anyway need it:
    Attached Files Attached Files

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I am looking at doing a database to track development approval applications (I am a town planner).
    Further to the advice provided in the posts above, I think you are getting ahead of yourself when focusing on customized autonumber fields at this time. I recommend you work though a tutorial or 2 from RogersAccessLibrary identified in this link. You will learn and experience the process of designing a database and some related database principles.

    Good luck with your project.

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

Similar Threads

  1. Replies: 2
    Last Post: 09-29-2019, 07:26 PM
  2. How to set an autonumber to an specific value?
    By Secue in forum Database Design
    Replies: 2
    Last Post: 08-20-2013, 12:09 PM
  3. Format an AutoNumber in Access
    By SelfLearner in forum Access
    Replies: 1
    Last Post: 05-29-2013, 10:42 AM
  4. AutoNumber Field Format
    By andy-29 in forum Access
    Replies: 5
    Last Post: 11-13-2012, 07:31 PM
  5. Using AutoNumber to issue specific numbers
    By capnpat in forum Access
    Replies: 8
    Last Post: 04-13-2011, 03:57 PM

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