Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jun 2001
    Location
    Bethlehem, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL question (Access 97)

    I have a database where I track tasks.. One table has the task information. Another table has a task number base on the year that the task starts. I would like to automatically create a task number that would have the year the task starts and the next sequential number for that year.

    Example: If the task starts on November 11, 2002 and the last number assigned for that year is 100 the new number would be 02101. IF the task starts on January 11, 2003 and the last number for that year is 002 then the new number would be 03003.

    I imagine that I should use a SQL statement to achieve this but I am not sure how this is done.

    Any help would be appreciated.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: SQL question (Access 97)

    This can get a little tricky. First of all, you have to be ABSOLUTELY sure that you will never have more 999 tasks in a year!!! If there is that possibility, then you will have to go to a larger number.

    The logic you need to employ requires that you find the current largest number that is greater than or equal to the first possible task# for that year, and < than the next year. Something like this:
    <pre>NZ(DMax("taskNo","tblTasks","TaskNo >= year(Taskyear)*1000 AND TaskNo < (year(taskYear)+1*1000"),year(taskyear)*1000)</pre>

    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: SQL question (Access 97)

    If you make the task number field a text field then you don't have to worry about the 999 limit.
    It will happily move from 02999 to 021000 if you just increment the 999.
    To do this yoiu need to pull the year out, convert the remaining bit to an integer, increment , then put back together again. All this using the largest current number that you have found somehow.
    Regards
    John



  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL question (Access 97)

    However, if you use a text field, you have to remember that the values will sort in alpha order, not numeric.
    Charlotte

Posting Permissions

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