Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts
    We are using Access 2007, with a database that has been saved as Access 2002-2003, running on Windows XP

    We have lost some leading zeros when appending records to a new table.

    The data originally came from an excel spreadsheet. The format in the spreadsheet for the zip code field was "special - zip code" and all zip codes begin with a 0 (we are located in Maine, USA).

    We imported the data from Excel into an Access Database and had it create a new table for itself. The datatype for the zip code field was "text" and the 0s all showed, just as expected.

    Next we appended the data to an existing table, but in the existing database, the leading 0s all disappeared from the zip codes, leaving only the final four digits. No other data appeared to be altered in any way when it was appended.

    The really strange thing is that we did the same thing with another Excel Spreadsheet with the same datatypes in both the original spreadsheet and the table it created in our database, and when we appended that data to the same existing table, the leading 0s are still there!

    How do we fix the missing 0s from the first append (and any clues how this happened? A search of this forum brings up nothing for either "zero" or "zip")?

    Thank you,
    -cynthia

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I have no idea what caused this to happen, but you can try the following:
    • Create a query in design view based on the table that contains the zip codes with a missing leading zero.
    • Add the zip code field to the query grid.
    • In the next column, enter the following expression:

      Len([Zip])

      where Zip is the name of the zip code field.
    • Enter 4 in the Criteria line for this calculated column.
    • The query should now return all zip codes with length 4; you can check this by switching to datasheet view and back.
    • On the Design tab of the Ribbon, in the Query Type group, click Update to change the query to an update query.
    • Enter the following expression in the 'Update to' line for the zip code field in the first column:


      "0" & [Zip]

      where Zip again is the name of the zip code field. Leave the 'Update to' line for the second column blank.
    • Select Query | Run or click the Run button in the Results group of the Design tab of the Ribbon.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That worked!
    Thank you Hans.
    We sure are mystified, though, as to the source of the problem - but armed with the ability to correct it if it happens again we'll now forge ahead with our work.

    -cynthia

Posting Permissions

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