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")?