Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jan 2004
    Posts
    115

    Unanswered: Add Year to Custom AutoNumber

    I am currently using the DMax function for my custom AutoNumber
    I would like to add the year before the OrderID # (the year should automatically be changed based on the computers year/date)

    Table name = tblOrder
    Order number field = OrderID

    The OrderID should look like
    20065233
    20065234
    20065235

    The function should look at all digits of the orderID with the exception of the first 4 digits, and should generate the next orderID

    Does anyone have such a function ready?

    Thank you

    Tom

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Year

    Personally, I wouldn't put this as part of the autonumber but have another number field to store this. Is there a specific reason this needs to be part of the autonumber?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    as you add the 4 digits for th year (don't know why you want to aas its unlikely your system will survive for another 94 years...)

    As Paul infers why not make the last n digits be your number. effectively the year is redundant / derived data - it will already be indetifiable visa the order date. I have seen order number prefixes which identify a year and month - purely so that the manual file can be quickly identified- ie ADxxxxxx was year one month 4 order xxxxxx.

    when you request the ordernumber strip off the first 4 digits and search on the xxxxxx. the only proviso I'd ut on it is to make sure that you give enough space for the expeted growth of the system

    ie if you expect say 1000 orders per year and the system will last for 10 years then the minimum size the xxxxxx should be is 5, preferably 6 digits.

    if this is going to be a frequently used part of the system (ie you expect tobe placing many orders per hour then I'd be cautious using domain aggregate functions - it may be 'smarter' to use an SQL select satement in a recordset in preference.

    HTH

  4. #4
    Join Date
    Jan 2004
    Posts
    115
    Its always good to talk to smart people like you.

    I will not add the year into the ordered field.

    I created a new filed called RecordCreated with a default value Now()

    Now, I would like to combine them on the forms and report it should look like
    5233-2006
    5234-2006
    5235-2006

    How should the record source look for the ordered field?

    Thank you

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd sugest you place the formatting of the year into a function available to all forms, reports and queries.
    eg
    Code:
    function FormatMyOrderNo(OrderNo as long,OrderDate as date) as string
    FormatMyOrderNo=format(orderno,"0000000") & "-" & year(OrderDate)
    end function
    then when you want to use it set the value in the report as =FormatMyOrderNo([MyOrderNo],[MyOrderDate]) where the items in the square brackets are the colukns from the table or query.

    HTH

  6. #6
    Join Date
    Jan 2004
    Posts
    115
    Thank you

    I do not want to format the orderID (I do not want to add zeros to the orderID)

    I am still at step one. How do I display just the year ? Format(??????)

    Thanks

  7. #7
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Tom

    the format command work like

    0 = I want to see it weather it there or not
    # = I want to see it if something there

    format(123,"0000000.00") =0000123.00

    format(123,"##,###,##.00") =123.00

    Format(datevalue,"YYYY")

    or
    year(datevalue) <= looks @ the format setting of you comuter.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    tom
    the reason for suggesting the format is your original request was to display

    5235-2006

    the suggetsion for the format mask of the order number was so that you have a consistent number
    ie
    000001-2006
    001234-2006
    123456-2006

    if you are happy with
    1-2006
    1234-2006
    123456-2006
    then ddon't use the format

    if you just want to dispay the year of the order date
    then
    =year([RecordCreated])
    another variant is
    =format([RecordCreated],"YYYY")
    in effect its the same thing using a different method.

    incidentally I've often seen

    000001-2006
    001234-2006
    123456-2006

    espressed as

    AA000001
    AD001234
    AL123456

    where the first digit identifies the year, the second the month
    ive also seen companies use a psuedo random number after the true invoice number (eg the minute the invoice was created as a suffix
    - people are very adaptive and within the organisation they realise what these codes mean. It often masks small companies who are concerned that customers may realise how few invoices they are raising.

  9. #9
    Join Date
    Jan 2004
    Posts
    115
    Thank you for the clarifications

    I am happy with both methods bellow. I just dont know which of the 2 options is best?

    =[OrderID] & "-" & Format([RecordCreated],"yyyy")
    =[OrderID] & "-" & Year([RecordCreated])

    Thanks again

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    The change is negligable. I would probably use Year() just for readability purposes. I think format defaults to calling the same code as year() intrinsically anyways. Don't quote me on that though...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  11. #11
    Join Date
    Aug 2011
    Posts
    1

    How to generate alphanumeric item numbers?

    Hello!

    I am trying to do almost exactly what you described, plus I like the suggestion of first letter for year, 2nd for the month.

    What I am actually trying to do is automatically generate an ID for each completed new record (not the PK) starting with A001001. I've been told the A will not ever change, but I have my suspicions, so I would also like to know how to auto-increment that as well (when it hits A999999, the next record should be B000001).

    This would be occurring as the users add new records on the form.

    Any help will be appreciated!

    Quote Originally Posted by healdem View Post

    AA000001
    AD001234
    AL123456

    where the first digit identifies the year, the second the month

  12. #12
    Join Date
    Nov 2015
    Posts
    6

    how to use year as id prefix

    hello my friend i am working on a database and i want to make the id no look like 2006-5233 this. now because i am new to ms access i want to know the full procedure that how i can do it in easy way. you can mail me if you really have a written procedure and code, my email id is jai.kushwaha.92@gmail.com thank in advanced. i need it fast friends.....

  13. #13
    Join Date
    Nov 2015
    Posts
    6
    Quote Originally Posted by Tommiller View Post
    Its always good to talk to smart people like you.

    I will not add the year into the ordered field.

    I created a new filed called RecordCreated with a default value Now()

    Now, I would like to combine them on the forms and report it should look like
    5233-2006
    5234-2006

    How should the record source look for the ordered field?

    Thank you

    how i can do it friend i mean somehow i want to make my id no. look like 5234-2006. but i don't know how to do it i want your help.

  14. #14
    Join Date
    Nov 2015
    Posts
    6

    how to automatic start the ID no from 00001 on every 1st junaury

    hello friends now i got the idea behind it that how to make 01234-2006 type of IDno but now i want that every 1st of the January the IDno start again from 00001 and on the 1st January 2007 the IDno Look like 00001-2007. so how we can make any suggestion and help??? i need it fast friend.

    thanks

  15. #15
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Msaccess (database) like to have a PrimaryKey with NO Duplicates

    So You can't set ID Start at 1 on the 1st of jan because you have the same ID next year and if they are related to and other table you be ......


    So i always have a PrimaryKey But I dont use it for anything (msaccess happycamper)

    What I would do and Have Done

    1. create A ID table That Store the ID number then When you create a New Record read the data from the ID table add 1 to it save the record and update the ID table

    2. if newrecord then find the Max record for said year add 1 save it. but if day =1 and month =1 and year=year then ID = 1

    also I would just store the Number in the table and do the formating when you want to see the out come

    in a query
    bla:format(ID,"0000") & "-" & format([Adate],"YYYY")

    or write a function to format it and display the data when you need it.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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