Need to store zip code as text, but system keeps storing as decimal value 1

[VicM]

Hi,

In Access 2010 DB, I have a main demographic table that contains a person's zip code. In this table the zip code is stored as text since the spreadsheet used to update the data has its zip code column formatted as text because creators of the source spreadsheet can also store the + 4 portion of the zip code which contains the "-" between the first five digits and the last four. Also, several state's zip codes begin with a zero (0). To preserve this, the data needs to be stored as text.

A differnt spreadsheet source does not contain the zip code so I need to search a zip code table based on the person's city/state. In the zip code table the zip codes are defined as text, but are only 5 digits; they do not include the + four values. But when I search for the zip code in this table and store it in a newly created, interim table (which will then be used to batch update the main demographic table), the zip code is stored as a decimal number with two zeros after the decimal point.

I've tried using CStr to convert the value (which already is a string) before adding it to the interim table. This didn't work and the value was still stored as a decimal number.

Short of redefining all the zip codes in the zip code table as 6 digits with a '-' in the sixth position and then storing the Left(zip,5) into the main demographic table, is there another way of handling this situation?

Thanks,
Vic

 

[SkipVought]

Hi,

But when I search for the zip code in this table and store it in a newly created, interim table (which will then be used to batch update the main demographic table), the zip code is stored as a decimal number with two zeros after the decimal point.

How are you storing this value and where?

I'm guessing that you took the numeric TEXT VALUE and assigned it to a cell.

Faq68-7375.
Faq68-6659.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

A differnt spreadsheet source does not contain the zip code so I need to search a zip code table based on the person's city/state.

The person's city/state is not enough to establish ZIP code for that address. My city of about 50,000 people has at least 3 ZIP codes, and I am sure bigger cities have a lot more ZIP codes.

"the + 4 portion of the zip code" - that is totally different 'animal'

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[xwb]

Have you tried something like format(zip,"00000") or right("00000" + str(zip), 5)

 

[VicM]

Andrzejek,

Thanks for your input.

The US Postal Service provides tables for each state listing all its zip codes (5 digits only) along with the county, primary city and acceptable cities columns. But you are correct. In the region where I live, the 3 major cities each have multiple zip codes (and interestingly, one of those cities straddles two counties!). Of the data I'm trying to associate with a zip code and county, about 95% are located in areas with one zip code.

In order to provide the additional +4 zip code, the table would also need to include the street addresses. If you've ever seen the size of the zip code publication at the post office (do they still have those?), you will understand how large a table that would be. And so far what I've seen offered online, none include street addresses. Also, many have costs associated with downloading. So for my client's purposes, the 5 digit zip is often sufficient. They will have to decide what to do with their members who live in areas that have multiple zip codes.

I've managed to create code to extract that data, such as it is.

Vic

 

[combo]

How do you create the "newly created table" with zip code? If it is access, why can't you define this field as text?

combo

 

[SkipVought]

I thought this thread was about text vs numbers with resect to zip codes in this instance.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[VicM]

True enough Skip. That was a digression.

combo, after the spreadsheet is modified to the data and format I need, I perform the following:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tblImportData", filepathXLWk, True

So the table is created from the DoCmd method. I don't know if it's possible to define the format of the columns from that.

Vic

 

[SkipVought]

I would be PULLING the data from Access in Excel via a query, a ONE TIME design event in an Excel workbook that spins off another file that would have all the PRE-formatting for this TEXT column. You could code it, for instance to run the query & spinoff on the Open event.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[VicM]

Guys,

Actually I just tested modified code based on combo's post.

When I create the column in the existing spreadsheet to house the zip codes, I format that column as text before adding data to it.

[b][COLOR=#204A87]impfile.Application.ActiveWorkbook.ActiveSheet.Columns("AL:AL").NumberFormat = "@"[/color][/b]

I then ran the subroutine, and lo and behold the imported data to the table was indeed in text format! Eureka!

Thank you all for your comments and suggestions. At least I got over that hump.

There now is another I recently posted in Visual Basic (Microsoft) Databases. Hopefully someone can guide me with that one.

Thanks again,
Vic

PS Is there a way for me to close this post?

 

[SkipVought]

Don't forget to give combo a well deserved little purple star. Helps other members identify helpful posts.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[VicM]

Skip,

Done!

Vic

 

[combo]

Thanks. [tt]DoCmd.Transferspreadsheet acImport[/tt] either creates a table or, if the table already exists, appends data to existing table. If you like to import data this way, import table for the first time, delete data, switch to design view of the table and format fields. To delete all records before import: [tt]DoCmd.RunSQL "Delete * From [TableName]"[/tt]

combo

 

[VicM]

Thanks combo.

But I think because the imports come from two different sources with two different formats, I went with the current method.

Yet I suppose I can create two tables and use one for one source and one for the other; deleting the data instead of the table. I'll think about that.

However, I'm also trying to keep the size of the DB down. It's been growing over time and I'm attempting to make it as lean and efficient as possible.

Vic