east coast zip code problem

[n8kindt]

most areas on the east coast have zip codes starting with 0... you can probably see where i'm going with this already.

i'm trying to relate these 2 databses:

1) TaxDB - an ODBC MySQL database (which is mainly used for our webstore) of every zip code and it lists all their tax info for city, county, and state.

2) CustomerDB - the database i have created and that we've been using for two months to store sales data and contact information

attempt #1: my customer zip field was originally text. i then converted CustomerDB.zip to an integer field. i then created a relationship between the TaxDB.zip and the CustomerDB.zip and the only problem was the "0" of every east coast customer ended up being omitted by the conversion. there seems to be no way to add this zero. i tried every single number format possible (long int, double, single, decimal) and nothing works.

attempt #2: i decided to make a query that first converts TaxDB.zip to a string using the CStr function. unfortunately, everyone of the east coast zip codes ended up returning #error while all the other zips worked fine.

so.... i am not sure what there is to do. is there a function i can use to convert TaxDB.zip to a string that properly converts the zero in string format? i am at a loss. i'd appreciate some input!! thanks guys

--nate

[Megalog]

try:

format([Zip code field],"00000")

That will enforce the 5 digits, whether it starts with a zero or not.

[RuralGuy]

In addition to Megalog's excellent suggestion the rule of thumb is: "If you will not be using the numerical field for a math function then make it a Text field".

[n8kindt]

try:

format([Zip code field],"00000")

That will enforce the 5 digits, whether it starts with a zero or not.

this didn't exactly work directly but i got it to work indirectly... rather than using this code on CustomerDB.zip i used it TaxDB.zip. rather than converting the number using

CStr([zip])

i used

CStr(Format([zip], "00000"))

i have my theories on why this worked but it would be hard to explain. basically, i believe access was returning an error on the zip codes that started with zero b/c it automatically converted the number into an integer BEFORE it converted it to a string. and so when the string came out and didn't look like what it started as, this triggered the #error to show.

[n8kindt]

In addition to Megalog's excellent suggestion the rule of thumb is: "If you will not be using the numerical field for a math function then make it a Text field".

excellent advice. i definitely learned that for the future. unfortunately, the MySQL database was not created by me (and was part of a larger system--our webstore) so i couldn't do anything about the original number. i'm happy though b/c i got it to work perfectly. i just saved the company a bunch of money so we don't have to manually figure out whose zip code goes to what county and state and manually crunch the numbers accordingly ...a job that normally took 2 weeks is computed in 2 seconds :)

thanks guys for all your helpful replies!!

[RuralGuy]

Excellent! Glad we could help.