Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2007
    Posts
    70

    Unanswered: Formating an autonumber?

    Hi,

    Can you format an autonumber so that it is always a certain number of digits long? ie instead of 1 it would be 0001?

    Also could you set it to have the year (YY) at the front? ie in the above example it would be YY0001 so this year it would be 080001.

    Any ideas?

    Cheers

    OB1

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Re

    Auto number not longer than 4 digets : so you cant get higher than 9999

    Then you want it to display 2 digets of the year in front of it

    Why do you want an Autonumber in the first place ???

    Write your own function to do it. And place an in a unique db.field And index it.
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  3. #3
    Join Date
    Dec 2007
    Posts
    70
    Hi,
    Thanx for your post but that seems a bit too time consuming for the minimal benefits. It was just an idea not needed though.

    How about just showing the 0's? ie 0001 as the first autonumber?

    The autonumber is used as a sample number to identify all the samples in the lab with a unique number, but we dont get anywhere near 9999 each year!

    Cheers

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so have a look at the format function........

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Or the Format property.

    The point is moot anyway isn't it? You can't just reset an autonumber for the "next year" anyway, so it all seems quite irrelevant.

    I'd just use a normal Autonumber (for the PKF) and a Date field.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Dec 2007
    Posts
    70
    Hi thanks for your posts,

    At the end of the year we delete the sample table and add a new one, so we would be able to reformat the autonumber each year! Though you are right about it not being important

    I have tried using the format property but didn't know what to put in it to format the autonumber how I would like ie 0001?

    What do I type in the format property to get the four digit autonumber?
    And should I do it at table or form level?

    Cheers,
    OB1

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You would just store the autonumber as normal. The formatting is just on display. If you need the year of record creation then stick that in a column. If you want the current year (though that doesn't make much sense) generate on the fly.
    e.g
    Code:
    RIGHT(YEAR(DATE()), 2) & RIGHT("000" & MyAutoNumber, 4)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what have you tried?
    where are you storing your number / numbers?
    how are you assigning the value of the number?
    where are you building your number?

    that will dictate how and which method is most appropriate for your needs
    it may be that what is most appropriate is (year(now())*10000)+myval

    it may be that a format mask is most appropriate
    oit may be both, especially if you want 080001


    if you want the formatting then its often best done in the table, but then again this sort of thing probably isn't appropriate in a table.. as you will be duplicating the data.. you cannot assign a value to an autonumber column... so the best you can hope to do is to store the year separately and merge them later, unless you go down the daft route of strong the automnumber value as a column and the year + autonumber as another column, in which case a table format may make sense. otherwise its going to have to be a format mask on the field, or format function on assignment to the control.

    but then again why bin the previous years values.. after all the primary column will be unique....

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by healdem
    if you want the formatting then its often best done in the table
    Begging to differ sah
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    fair enough
    but if you are using a consistent format across all forms and reports and you are a beginner then Id argue its best int he tbael.

    if you are a hardened pro, <creepalert>like yourself </creepalert>then I'd suggest formatting the individual elements.

    for some people formatting is a pain, so definition in the table means that that format definition is common across all forms, reports etc.... providing of course you haven't started designing reports and forms before deciding to change the formatting and / or add columns to the table definition

    yer pays yer monery, yer taker yer choice

  11. #11
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Wink re

    The autonumber is used as a sample number to identify all the samples in the lab with a unique number, but we dont get anywhere near 9999 each year!
    hmm from this i can tell that you will use it for a labeling system or somelike

    Would just make an Autonumber field + an other field where you save your "Label" value

    get the id (the autonumber field) if you are sure you dont get higher then 9999, then format the field in code
    Format(id,"0000") stick the year before it

    so your code will be
    Code:
     right(year(getdate()),2) & Format(id,"0000")
    REMARK : if your code on 31 dec 2008 = 086345
    the next day your code will be 096346

    REMARK 2 : if your id grows bigger then 9999 i.e. 089999
    then next code will be 080000 (id = 10000)
    and the next after that 080001 same as the first one (id = 10001)
    But if you jumped a year its 090001 (id = 10001)
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I can only imagine you logged off to jump in the shower Mark
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by OB1
    I have tried using the format property but didn't know what to put in it to format the autonumber how I would like ie 0001?

    What do I type in the format property to get the four digit autonumber?
    And should I do it at table or form level?
    Simply "0000" and I would do it at the form/report level personally.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  14. #14
    Join Date
    Dec 2007
    Posts
    70
    Do you ever get one of those days (or weeks in my case!) where your brain seems to detach itself from the rest of your body?!?
    I seriously thought I'd tried everything to get the four digits and all I needed was '0000'!!
    Unbelievable! The only thing I didn't try and it should have been the first thing I did try!!

    I think it's me that needs the shower pootle!! A very cold one!

    Thanks guys

    OB1

  15. #15
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes, I'm sure we all have times like that!

    Enjoy the shower ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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