Results 1 to 7 of 7
  1. #1
    PJackson
    Guest

    dates and Autonumbering

    How can I create an autonumbering system that 1) uses the last two numbers of the year, then a hyphen then up to a four digit number (YY-###1,YY-###2 etc). ACCESS 2000 says I can customize my date/time format but there is only the standard Short(1/2/97), Medium ( 2 Jan 99) and Long date (January 2, 2000) It also said that I could only use the versions that are setup in my regional settings. Again, those are only the standard short, medium and long dates. When I try to join the two digit year with the numbers as/in a query, I get an Type mismatch error. Is there a way to get what I want?

  2. #2
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dates and Autonumbering

    I would hesitate storing this sort of thing in a table as it can usually be calculated in a query. Is there a date associated with each record? If so you could use the year function or right function to return the 2 digit year portion of the date along with a combination of padded zeroes and an autonumber field. Post back if you need help with the expression.

  3. #3
    PJackson
    Guest

    Re: dates and Autonumbering

    Thank you!! And yes I would need help with the expression. I'm very new at all this with no training!! Could a query create it then store it in the table? You see this number is the way we track letters and forms we send out. 01-0001 Would be the first letter sent out for the year. The next letter would automatically show up as 01-0002 etc. Also, there are two tables one I can't change the formatting on and the other I created. Management wants to track both tables with one set of autonumbering. Can I join both tables by query shown in a form that could track that way?

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

    Re: dates and Autonumbering

    You CAN customize date formats. For example, "m/yyyy" formats 4/1/2001 as 4/2001. What you're trying to do isn't customizing a date format, it's creating a hybrid string key based on a date with an incremental number concatenated on the end.

    If you want to actually store it like that, then the easiest way to handle this is to create a function that you pass a date to. That function should lookup the largest key number for that date, increment it by one, and pass the string back to you. Alternatively, format an actual date field as "YY" and store the left padded string in another field. You can always concatenate them on the fly.

    You can only join the numbers as a string, especially since you want a hyphen in the thing. Get it firmly in your mind that these are NOT numbers or dates, they're strings and you can only link them to equivalent strings. However, if you keep the formatted date in a separate field, you can always compare it to other dates using the Year() function.
    Charlotte

  5. #5
    PJackson
    Guest

    Re: dates and Autonumbering

    Thanks for your response. Not quite sure how to proceed but will work on it.

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Winnipeg, Canada
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dates and Autonumbering

    Try this:

    Public Function NextSeqNum(myDate As Date, myLastNumber As Long) As String
    'usage: NextSeqNum(Now(),3)
    'returns: "01-0004"

    Dim CurrLen, Loops, i As Integer
    Dim strYear As String
    Dim strNum As String

    strYear = Right$(Year(myDate), 2)
    strNum = CStr(myLastNumber + 1)
    CurrLen = Len(Trim(strNum))
    Loops = 4 - CurrLen
    For i = 1 To Loops
    strNum = "0" & strNum
    Next i
    NextSeqNum = strYear & "-" & strNum
    End Function

    KenM

  7. #7
    PJackson
    Guest

    Re: dates and Autonumbering

    Ken -I will try it! I have no training in this area so I'm just blundering through! Thanks for your help!

Posting Permissions

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