Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2014

    Unanswered: losing leading zeros when updating number field with text

    We are using Access 2007 to maintain a database of virus testing data. Our records are numbered sequentially in the first field. Lower numbered records having leading zeros, so that all sample records have the same number of digits (i.e. 0001-9999). This has been achieved by specifying 0000 in the field properties (General tab, "Format") and the Data Type as number.

    We would like to use these data tables with the numbered records to generate tables in which test information is appended to the record number:

    e.g. Cvd4p12-0001

    We have been doing this with an update query (i.e. prefixing the record number with the text "CVd4p12-"), but this has required the updated field to be formatted as text rather than as a number (for obvious reasons). In doing this we lose the leading zeros on the record number, so rather than:


    we get


    Is there an easy way to tell Access to keep the leading zeros from the record number field when it places that information into a text field?

    So far we've been adding zeros to the Update Query text prefix (e.g. "CVd4p12-000", "CVd4p12-00", etc.) but that requires running the query separately for each batch of 1-, 2- and 3- digit numbers. Tedious!

    Thanks for any help you can offer.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    So by the sounds of it the data is being stored as an integer but being displayed / formatted with keading zero`s
    When you merge the text with the number use the format function.
    Select mytext & format(mycolumn,"0000") as theformattedvalue from mytable
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2014


    Thanks, healdem. That was super helpful. Problem solved!

Posting Permissions

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