Query w/ zip codes, leading 0 issues

[jguttman64]

Hello,
This is my first post and I'm relatively new to Access -- so please be gentle. I have a table containing 25,000+ records (basically, contact info) and I need to extract 30 to 40 groups of approximately 25-50 specific zip codes and save them.

My problems have been in getting the zip codes to import from excel without Access dropping the leading "0" and building the query.

Any help would be greatly appreciated. If there is a simpler way in excel, that would be fine to. I don't want to have to type in 25-50 zips using "find".

Thanks

 

[Scooterbug]

I've encountered this problem before. Several different ways of handling it. One way is to first format the cells in the spreadsheet as Custom, and put in 00####. Then, insert a row at the top...and put some text in the top of the column that has the zipcodes.

Or you could save the file in .csv format

What happens is when Access pulls the data in, it guesses for you what the data type will be based off of the first row. When you dont have text in the first column that has the zip codes, Access assumes they are numbers...so drops the leading zero(s)

 

[boblarson]

My problems have been in getting the zip codes to import from excel without Access dropping the leading "0" and building the query.

Import to a predefined table which has the zip code set up as TEXT not number which is what it is likely currently going to.

 

[jguttman64]

I thank you both very much for your responses - however; I am still having some difficulty.

Scooterbug - I have repeatedly tried to format my excel column with custom "00000" and "##000". Custom "00000" shows the zips correctly in excel, but as soon as I hit save, excel is switching the format back to special "zip codes". Making it impossible for me to import it as custom. I see no way to import a .csv file into Access.

Boblarsen - I have established the table by importing the excel, then deleted all of the data and switched the Data Type for the zip filed to text and attempted to import into that existing table -- it still dropped the 0's.

 

[boblarson]

Can you post the database and a copy of the Excel sheet (with just a few rows)?

 

[jguttman64]

boblarson - the excel file is attached, but the db is 52MB -- it's kind of odd because there are only a few tables in it, and none are very big -- do you want me to attach it?

thanks

 

[boblarson]

boblarson - the excel file is attached, but the db is 52MB -- it's kind of odd because there are only a few tables in it, and none are very big -- do you want me to attach it?

thanks

First things first -

1. Run Compact and Repair
in Access 2003 that is in Tools > Database Utilities > Compact and Repair Database
in Access 2007 it is under the Big Round Office Button > Manage > Compact and Repair Database

2. Then ZIP the file, if necessary. If you have WinXP or Vista you can just right click on the database file and select SEND TO > COMPRESSED FOLDER and that will zip it. Upload the ZIP file.

 

[jguttman64]

boblarson - thanks.
The file is attached.
I really appreciate all your assistance!

 

[boblarson]

I am looking and boy, it sure is being a pain. I haven't figured out why yet but I can see that it is a real pain right now. It isn't acting at all like it really should.

 

[jguttman64]

I'm sorry it's difficult, but I'm glad I'm not crazy -- yet.
Thanks for your time.

 

[Scooterbug]

Jguttman,
To save as a .csv file, open up the spreadsheet and go to File --> Save As...

In the Save as type field, select .csv I've tried it, and I was able to import it sucessfully. Just make sure that when you are importing the .csv file, you specify that the field that holds the zipcodes is Text, not long integer. That should do the trick.

 

[chipcain]

By now you may have your answer but another possible option would be to create a query that uses a calculated field similar to below: In this case your zipcode field.

fFIELDNAME: Format([FIELDNAME],"00000")

If you wanted a ten characture field just use 10 zeros. This will also work with fields that have decimals.

fFIELDNAME: Format([FIELDNAME],"00000.00")

 

[jguttman64]

I want to thank everyone for their help!

 

[bolson7117]

=Right([Zip],5)

This gets the last 5 digits of zip with no leading 0

 

[jguttman64]

Thanks. I'll try that.

 

[Scooterbug]

=Right([Zip],5)

This gets the last 5 digits of zip with no leading 0

The problem he was having was that Access was dropping the leading zeros when he imported the zipcodes. Using Right([Zip],5) is irrelevant to his situation.

jguttman64, have you gotten the problem figured out yet?

 

[timo1999]

I think the suggestion of adding the "00####" to the first row was on the right track. I have had this problem before, and all I did was instead of changing the format for the column with the zip codes in Excel, I would type in a word like "bogus". As long as that record was in the first row, Access would import that field as a text, and all you would have to do is delete the bogus row.

tim