Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2008
    Posts
    150

    Unanswered: Problem with Storing Formatted Numbers

    Hello,

    I have added this format “IGSP000000” to my AutoNumber field to automatically generate an incremental work order number. The format displays fine, but does not store the data as formatted. For example, I need the stored data for record 1 to appear as “IGSP000001”. Although the data is displayed in the field as“IGSP000001”, it is stored as “000001” without the letters “IGSP”.

    How can I get the entire formatted number to store in this format “IGSP000001”?

    I have exausted my knowledge on this one.

    Appreciate any support I can get. Thanks.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    an an autonumbered column you can't. autonumber columns are integer (well in reality they are integer sized as bigint/long datatype).

    is the IGSP a common prefix.. ie is there any other prefix used.
    if you only use IGSP then leave your design as is and alwasy prefix the autonumber column with IGSP whenever the user sees it, and always remove the IGSP before storing the number

    the alternative is store the prefix separately as an additional column in the db. you request the whole reference, split it into its constituent parts and carry on as normal

    the third option is to create your custom numbering scheme and store the whole thing as one value. but that means you have to write your own routines to get the next available number for that prefix or whatever. if the number part has significance outside the system then you may well be best to use this technique. By significance I mean that you must have contiguous numbers with no gaps, or numbers out of sequence.

    just as an aside, you dont' store formatted values in a db, you store values and then format them in the front end or the application that the user uses. like all rules there are exceptions, but you will get off to a better start if you presume that formatting is the preserve of the front end, not the data store. you can do some formatting when you extract the data from the data store, but it should not be formatted IN the datastore. formattign often results in textual/string representations of the data to a computer the currency value of £12,345.67p is not the same as 12345.67, attempting to store values as a text/string datatype will lead to all manner of problems, reduce the effectiveness of the query engine. another one to watch for is dates. store data in the base datatype dates in datetime, numbers in number and so on

    HTH
    Last edited by healdem; 03-12-09 at 04:05.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2008
    Posts
    150
    Well, now I know why.

    Thanks for the reply and storing information HTH. Your reply was VERY informative.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    HTH is a very helpful character
    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

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Very good post healdem!

    I wonder if your well written reply shouldn't be in the code bank (under a tips on autonumber type post.)
    Last edited by pkstormy; 03-12-09 at 23:26.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by StarTrekker
    HTH is a very helpful character
    and possibly full of hope as well.....


    HTH: Hope That Helps
    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
  •