INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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

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

Hi,

Quote:

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: Be Aware: Excel can Change Your Data.
FAQ68-6659: When is a NUMBER not a NUMBER?.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

Quote:


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.

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

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

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

(OP)

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

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

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

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

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
True enough Skip. That was a digression.

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

CODE

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

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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.

CODE

impfile.Application.ActiveWorkbook.ActiveSheet.Columns("AL:AL").NumberFormat = "@" 

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?

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

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
Skip,

Done!

Vic

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

Thanks. DoCmd.Transferspreadsheet acImport 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: DoCmd.RunSQL "Delete * From [TableName]"

combo

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

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close