Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Nov 2001
    Location
    Newark, New Jersey, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    New at this - autonumber with year (2000 sr-1)

    Okay, I'm new with access, so just learning some of the basics.

    How do I create a field (key) that automatically generates a number with the current year included. ie. 2001-0001, 2001-0002, etc.

    Any recomendations on good useful books or web sites for learning access?

    Thanks,
    Rich

  2. #2
    Star Lounger
    Join Date
    Nov 2001
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New at this - autonumber with year (2000 sr-1)

    I would suggest two fields. An Autonumber field and a Year field. Make the Autonumber field your Primary key and if you want to concatenate the Autonumber with your date for display purposes you can do that too.

    hth,
    Jack

  3. #3
    Lounger
    Join Date
    Nov 2001
    Location
    Newark, New Jersey, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New at this - autonumber with year (2000 sr-1)

    I'm going to need to be able to search by the combined field as well. Is there a way to combine two fields into one and append it to a different table perhaps?

    Thanks,
    Rich

  4. #4
    Star Lounger
    Join Date
    Nov 2001
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New at this - autonumber with year (2000 sr-1)

    In your query you would use two Criteria. One for the Year and the other for the range of records or the specific record.

    Hope that makes sense....

    Jack

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New at this - autonumber with year (2000 sr-1)

    Question: Do you want the following
    2000-0001
    2000-0002
    2001-0003
    2000-0004

    or does the non year part reset to 1 for the new year to give you
    2000-0001
    2000-0002
    2001-0001
    etc.

    If you want the 'reset' then you can't you an autonumber field. You would need to find the highest number for the year and then increment it to generate the next number.

    The Access developers handbook it a good reference book (but massive) by Lutwin, Getz, Gilbert.

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New at this - autonumber with year (2000 sr-1)

    This code will give you numbers like 2001-1, 2001-2 ...
    You need two fields, one for an IDNumber (number which will be incremented) and one for the ID (text) as listed above.

    Dim vCurrentDate
    Dim vCurrentYear

    vCurrentDate = Date
    vCurrentYear = Year(vCurrentDate)

    Me!IDNumber = DMax("IDNumber", "yourTableName") + 1
    Me!ID = vCurrentYear & "-" & Me!IDNumber

    You can call the event from a form you are using to add records. As Andy explained, you will need to adjust it if you want the numbers to reset each year.

  7. #7
    Lounger
    Join Date
    Nov 2001
    Location
    Newark, New Jersey, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New at this - autonumber with year (2000 sr-1)

    This worked well, thanks.
    If I did want the number to reset each year, what's the best way to tackle that task?

    I appreciate everyone's help.
    Rich

  8. #8
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New at this - autonumber with year (2000 sr-1)

    I did not test this, but it seems you would need to place an If statement around the code that assigns the values. Read the first 4 characters of the last ID in the table. If it matches vCurrentYear, use the code that assigns the value as is. Else, write code that assigns ID by substituting a 1 for the Dmax statement value.
    Check the data type of vCurrentYear, you may need to do a conversion to get the match.

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: New at this - autonumber with year (2000 sr-1)

    We do this in a production database (don't ask why!), and found there are some gotchas you need to watch for. For one thing, a user's PC clock isn't always right, so you want to be cautious about using dates to do thing automatically. We have one PC in a client network that every so often decides to set its year to 2020 instead of whatever the current year is. Moral of the story - use a message box to prompt the user and ask if it is OK for the system to reset the counter to start over because the year has changed.

    Another issue that can be a problem is if multiple users are adding records and doing it frequently, using the DMax function can get you in trouble (and if you table get's large can get slow). In those situations, we use a separate table that stores the number of the next record to be added. When we add a record, the first thing we do is increment that number. Then we can add the new record at our leisure and not worry about ending up with a duplicate or some failure to store a record because someone else got the same number. You might also consider storing the current year in the same one record table that stores the number of the next record.

    Hope this makes sense and gives you some useful guidelines.
    Wendell

  10. #10
    Lounger
    Join Date
    Nov 2001
    Location
    Newark, New Jersey, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New at this - autonumber with year (2000 sr-1)

    Thanks for the tips... out of dumb luck, I did create a separate table to hold just the number sequencing. However now, when I try to use the number (through expression builder) as the default value in a form with a different table, I constantly get #Name?. I tried playing around with a bound field, an unbound field, but the same results. Even just creating a blank table and using a value of 1 came back with the same result. Any suggestions on what I'm missing? Is this something I can do through vb code instead of expression builder?
    Any advice would be appreciated.

    Thanks,
    Rich

Posting Permissions

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