Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Nov 2005
    Location
    London, Gtr London, United Kingdom
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Auto Number (Access/2000)

    I was wondering how i would get access to create an autonumber that follows the following form. e.g. E0906-001. E is the letter representing May (i.e. A for Jan, B for Feb and so on), 09 is todays day, 06 is todays and the 001 is a sequentional number that that goes back to 001 at the 1st of each month. So if i was to generate a record today, i would want the number E0906-001, and if i was to generate another record on Thursday, the number would be E1106-002. Does that make sense.

    Is there some sort of formula for doing this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Auto Number (Access/2000)

    > ... 06 is todays and ...

    06 is todays what? Two digit year? Or something else?

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Auto Number (Access/2000)

    I don't think there isa method of creating a number which restarts each day. The only way I have found to do something similar is to have a table in which a counter and date are stored. Before adding a record, check to see if the date in the counter is the same as today. If it is add one to the counter, if not, set the counter to 0001 and use that number.

    As for the first part of the number, if should be possible with a little string manipulation - something like:

    ReferenceNo = Mid("ABCDEFGHIJKL",Month([Date()]),1) & Format(Date(),"dd") & Format(Date(),"yy") & "-" & [RefCounter]

    Hope this helps

  4. #4
    Star Lounger
    Join Date
    Nov 2005
    Location
    London, Gtr London, United Kingdom
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Auto Number (Access/2000)

    Sorry. bad typing in my haste. yes... 06 is a 2 digit year.

    Thanks Jaf90. what you've put mades sense and i'll have a bit of play with that.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Auto Number (Access/2000)

    Do you really need such an artificial format as E0906? If you can live with 050906, you could use the current date formatted as mmddyy; for E0906 you'd need extra code as suggeted by jaf90.

  6. #6
    Star Lounger
    Join Date
    Nov 2005
    Location
    London, Gtr London, United Kingdom
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Auto Number (Access/2000)

    It's to fit in with an existing filing system that's been around for years, so sadly it can't really be changed. I can't find out the reasoning for it either!

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Auto Number (Access/2000)

    The attached database demonstrates a possible approach. The table doesn't contain the "autonumber" field in the format you want, but instead it contains a date field that defaults to the current date and a sequence number field. The query uses jaf90's suggestion to assemble the "autonumber" in the required format. The Before Insert event of the form based on the query calculates the next sequence number the moment the user starts to type something in a new record.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •