Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2006
    Posts
    9

    Unanswered: Automatically Generated Formatted Code

    I'm looking to have an automatically generated tracking number generated when a record is created. It's basically going to function as a primary key (each record having a unique value), but with a slightly more complicated format. The format is as follows and the the description is below: I-YY-XXX

    I - Static Letter "I";
    YY - Two digit year depending on when the record is created. So if the record is created in 2007...YY would be "07";
    XXX - A three digit counter that resets each year. For example while records are added in 07, this field would just count up until Jan 1, 2008 and then it would begin counting again from 001.

    So, the fourth record in 2007 would be: I-07-004.

    I could have the number generated when the record is saved at which point I could draw YY from a date field on the record and the search the db and count up records with the same YY and then apply the next XXX. However, I was hoping there was an easier way of doing this. Anyone have any ideas or thoughts? Your help is much appreciated.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Write a function to do it for you ... BTW, you're planning on only 1000 keys? What if you need more?
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Oct 2006
    Posts
    9
    Well it's really 1000 per year, and there's no way we'll need more, historically I don't think we've even broken 150/yr...how would I write such a function? Do i need to search records...record sets, etc.?

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    I think I would agree with Mike on this one. I usually ask the customer how many records they will be creating each year to get a rough idea, and then I add 2 decimal places. It's worked out to be a good rule of thumb so far. But it's your call.

    Some untested code that might work for you would be like

    Code:
    Dim PrimKey As String, YearCount As String
    YearCount = Format(DCount("*", "TblName", Format([Fld_Date], "yy") & " = " & Format(Date, "yy")) + 1, "000")
    PrimKey = "I-" & Format(Date, "yy") & "-" & YearCount
    Try using it, you can probably make it work for you.

    Just as a side note, whenever I generate my own primary key, I usually try to make sure that the number doesn't already exist. Do a dlookup before you assign it to make sure.
    Last edited by nckdryr; 02-14-07 at 11:21.
    Me.Geek = True

  5. #5
    Join Date
    Oct 2006
    Posts
    9
    Would this function go on a form or right into the table somewhere. I'm a bit confused as how to implement it.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think you are in danger of creating an artificial constraint here

    Personally I think it would be smarter to use an Auto generated nuber for the PK and then create your sequence number based say on a SQL MAX() query where the year was the same eg
    Select Max(<MySequenceColumn>) from <mytable> where year(<datecolumn>) = year (Now())

    I'd want to put a table lock to stop someone else addign a row whilst I was doing the read & write

    the I & 07 are redundant data.. the I is static the year bit can be derived form a date.

    Id use a sequence number.. that way round youd be bomb proof.... if say the business suddenyl started doing a lot more work than before..... of incase some wazzock in marketing decided the number shoul have yet more meaning adding.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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