Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2006
    Posts
    20

    Unanswered: Badly Need Help With A Tracking System

    Hello everyone...I was wondering if anyone could help me populate a textbox in a form using SQL in access that would put together data similar to the one below:

    "CY630700001"

    CY - as the calendar year
    '6' - being the last digit of the current calendar year
    '307' - being the n-th day of the year (this number is for 03-Nov-06)
    '00001' - is the 5-digit sequence number for that day

    Here's what I've found so far but it's not quite working:

    INSERT INTO tblTracker ( trcknumber )
    SELECT "CY" & Format(Date(),"mmdd") & "-" & Format(DCount("trcknumber","tblTracker","left(trck number,4) = format(date(),'mmdd')")+1,'00000') AS Expr1;

    When I add the 'CY' in the beginning, it stops working. I'm not sure how to take the number '6' from 2006 and add it to the statement. Any help would be gladly apprecitated. Thank you!!!

  2. #2
    Join Date
    Oct 2003
    Posts
    103

    Datepart is the answer

    How about
    CY = Right(DatePart("yyyy", Date), 2)
    Then add that to your string as you have?

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why not simplify it
    if CF is a constant, then ignore it, display on forms, reports etc, but consider hard coding it. If it sisnmt going to change then why store it

    build the rest of the string / identifier outside the SQL, do it in VBA - it becomes more portable

    avoid where possible domain aggregate functions, in my view they are spawn of the devil, fine for occasional use but not to be used too frequently (once a blue moon seems appropriate to me): reason performance.

    consider using an index on the number and do a select max if you must, or some other technique to store the next available number

    just because your user requirement is for a composite value, that doesn mean you need to store it as a composite value, consider storing each element separately and use a composite index (if you must, but you dont need to indexing each colun should work fine).

    do as little processing in the SQL as you need to. most of your exsiitng processing could be happily done in VB, rather than using significant amounts of string processing within the VBA: reason, makes your SQL more portable and more efficient, if you need to upgrade to a meatier SQL back end then its fairly easy to do so if you keep you SQL close to the basic standards.

    so the last digit of the year
    left$(year(now()),4,1)
    OR
    left$(year(<mydatevalue>),4,1)
    your dateno form a calendar for preference
    xxxxxx from select Max (ColID) from <mytable> where whateverparametersare required
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2006
    Posts
    157
    taking 6 from 2006, i might use

    strlastdigitofyr = right(format(date,"yyyy"),1)

    and I think I would rather pass the whole concatenated string to a variable before feeding it to the INSERT query

    INSERT INTO ... VALUES ( strCONCATENATED )

    something like that
    Only quitters quit!

  5. #5
    Join Date
    Apr 2006
    Posts
    157
    hehe, sorry for double-posting but the big one's answer is most truly apt, all you need is to store, possibly the current date and manipulate it only during display to show your desired format (His answer came in while I was typing my first post)
    Only quitters quit!

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by syntaxerror
    ....strlastdigitofyr = right(format(date,"yyyy"),1)....
    ...oops good point, and well made
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2006
    Posts
    20

    Still confused...

    Thank you, Healdem and Syntax...unfortunately, I'm not very familiar with VB too much. I'm trying to ping my way through the codes but I am somewhat more familiar with SQL. However, if it is more efficient to use VB coding, I will try to work my way through it. I just thought that SQL was a bit easier. Since 'CY' was a constant variable; I thought all I had to do was add the string along with rest but when I do, it stops the sequencing part.

    I'm trying to get it to do the following:

    1) CY630800001 (the 1st issued number for 04-Nov-06)...CY630800002 (for the 2nd...) ...and CY....03 (for the 3rd)

    2) CY630900004 (for the next day, with the 4th item sequence)

    I'm not sure if explained it correctly. But it's a number I can place in a form that would autopopulate the textbox when a command button is clicked. I really, really would appreciate a hand on how to code this per se...Thank you so much!!!

Posting Permissions

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