Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 406
  • Last Modified:

Preserving Zip Code Formats

I have a file with USA zips codes in the 5 digit style (07897) and the 5 digit+4 style (07897-0875).  How do I format a table so it doesn't drop the leading zeros?

Also, will this format setting also allow non-USA zip codes with totally different styles in the same field (such as international zip codes with letters and more or less than 5 digits).
0
daisypetals313
Asked:
daisypetals313
  • 4
  • 3
  • 2
  • +1
1 Solution
 
IrogSintaCommented:
The Data Type of the zip code field should be set to Text and its size should be 10.
0
 
daisypetals313Author Commented:
This didn't work.  Zeros dropped.
0
 
IrogSintaCommented:
How are you entering the zip codes?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
mbizupCommented:
Ron is correct.

If you are using best practices with a split database, be sure to relink the back-end tables for that change to actually take effect in the front-end.
0
 
daisypetals313Author Commented:
Hi, I am not entering manually but rather importing an file.  I'm not sure what split databases means. I'm not sure if my file is that detailed.  I am starting with a blank database and importing an Excel file and the zeros are being dropped.
0
 
Dave BaldwinFixer of ProblemsCommented:
Excel is probably dropping the zeros.  If you declare the Excel column to be a number and then use formatting to keep leading zeros, it will still drop them when you export it.  Zip codes need to be text fields.  Phone numbers do too.
0
 
daisypetals313Author Commented:
I set the field in Excel to Zip code format so it has all the zips correctly.  It is when I bring them into Access that they disappear.
0
 
mbizupCommented:
Try linking to, rather than importing your excel file.

Set up a table, if you havent already with a TEXT field for the Zip code as Ron suggested, and the same field names as in your Excel file

Then run an INSERT query to get the data into your Access table;

INSERT INTO YourAccessTable SELECT * FROM YourLinkedExcelFile
0
 
Dave BaldwinFixer of ProblemsCommented:
Are you importing them into Char fields in Access?  If you bring them into numeric fields in Access, that too will strip leading zeros.
0
 
IrogSintaCommented:
Doing a direct copy and paste from Excel into Access should keep the formatting.  Try that out.
0
 
daisypetals313Author Commented:
OK, I have to accept this as a partial solution because it definiately helped get on the right track. The thing that made this work was something I have never tried before and this is setting the Zip code field into two different formats in Excel.  So I did Zip for the five digit zip codes and text for everything else.  Access marked the field as Text.  I don't exactly why this worked by I am so happy to have this now.  Thanks everyone!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now