Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    16

    Unanswered: format increment

    Hi all,

    first of all sorry by my english...

  2. #2
    Join Date
    Nov 2003
    Posts
    16

    Red face continue

    Sorry, i hit enter key... , but my problem is that i want to incremente a value like this: "0001-2003" ; 0002-2003; 0003-2003;.... But my problem is when the year change, i would like that in the next year the auto number started from 1, like this "0001-2004" ; 0002-2004.... "0001-2005"... I hope that you all comprehend. Thank you very much [[]]

  3. #3
    Join Date
    Nov 2003
    Location
    Virginia Beach, VA
    Posts
    4

    Re: continue

    Originally posted by PAFF
    Sorry, i hit enter key... , but my problem is that i want to incremente a value like this: "0001-2003" ; 0002-2003; 0003-2003;.... But my problem is when the year change, i would like that in the next year the auto number started from 1, like this "0001-2004" ; 0002-2004.... "0001-2005"... I hope that you all comprehend. Thank you very much [[]]
    Accesses Autonumber does not work with non-integer numbers. The number you want to use is actually a "string" value. You can achieve what you want to do using VBA code.
    1) Using DAO to access the table, you can OPEN the table you plan to save to and (using ".movelast") you can go to the last record in that table. Next you can read the previous value (ex. "0002-2003") that was saved for that last record. Save this value to a PRIVATE variable on you form's code page (Example: myvariable 1).
    2) Use the following example: "myvariable2=RIGHT(myvariable1,4)" to extract the YEAR from your number and save the year to a second private variable.
    3) Use a third variable to hold the current year (example: myvariable3=YEAR(date())
    4) Convert each variable to a string (to be sure you are comparing similar things) and compare to see that the year has not changed.

  4. #4
    Join Date
    Nov 2003
    Location
    Virginia Beach, VA
    Posts
    4

    Lightbulb Re: continue

    Originally posted by fynder
    Accesses Autonumber does not work with non-integer numbers. The number you want to use is actually a "string" value. You can achieve what you want to do using VBA code.
    1) Using DAO to access the table, you can OPEN the table you plan to save to and (using ".movelast") you can go to the last record in that table. Next you can read the previous value (ex. "0002-2003") that was saved for that last record. Save this value to a PRIVATE variable on you form's code page (Example: myvariable 1).
    2) Use the following example: "myvariable2=RIGHT(myvariable1,4)" to extract the YEAR from your number and save the year to a second private variable.
    3) Use a third variable to hold the current year (example: myvariable3=YEAR(date())
    4) Convert each variable to a string (to be sure you are comparing similar things) and compare to see that the year has not changed.
    <Sorry - Accidentally SAVED message before I was done>

    if cstr(myvariable2) = cstr(mevariable3) then 'the dates should match
    myvariable5)= LEFT(myvariable2,4)+1 'this variable holds the first part of new number you want to dave back to your table and you have just incremented the previous first four digits by one
    5) Using a last variable, you can build the new number to be passed back to your table as the new
    myvariable6= myvariable5 & "-" & cstr(myvariable3)

    Use code to start the count of the first 4 digits over at 0001 for the first instance of finding a new year. After that the same code will work.

    You can do this same process with more complicated code and fewer variables, but the way I have outlined here is easy to troubleshoot and be sure it works properly. Always remember to set each of these private variables back to "" AFTER the new counter value has been saved to your table. Hope this has been helpful

Posting Permissions

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