zip code formatting

I have zips that

The problem is the dash is on the field of the 5 digit zips. So the shortest zip is 6 characters. When putting the data on reports we do not want the dash to show on the 5 digit zips. But do want it do show on the 9 digit zips.

I am not sure how to code that. I am thinking I need a new field that is created from the original and drop the dash some how

Sponsored Links:

Related Topics

Zip + 4 to Zip 5 via LEFT, 5
I am trying to do the same thing as a =left(A2,5) command would do in Excel. Basically I have a zip +4 I need to convert to just the zip 5 for a lookup.

The field that has the zip + 4 is titled Receiver Zip. The field with the 5 digit is Zip 5. I tried using an update query but it didn't work.


City, State, Zip
I have a table that has City, State and Zip Code for most areas of the U.S. The zip code field selects Zip, city, and state, in that order.

For the City, it looks to zip.column(1) and state looks to zip.column(2). After updating the Zip Code field, the City updates automatically, but the State does not.


WildCards in VB Code
Are there wildcards in VB? For example, I want to run the following code.

DoCmd.DeleteObject acQuery, [Zip]

But the program name that I am referencing is on the form, and I want to delete any query that is associated with that zip. The query will definately have the zip in the name, but it may begin with query_, qry_, Q_, zip_Query, etc. Is there a wildcard that I could use to isolate the zip from my form, regardless of what comes before or after it on the query


Make the tab order of one or two fields conditional on the value in a preceding field.
I'm entering postal codes and zip codes on a form along with other contact information. If the contact is in Canada I would enter the postal code using and input mask. The next field is the US zip code where applicable.

If the Canadian postal code is filled I would like to skip over the US zip code automatically. Can this be done without writing code?


Query column return denoting records as top 80%, next 10%, & sums bottom 10%
I have a query that counts patient discharges by zip code and is in descending order. Adjacent, I've managed to create a column for the percent of total discharges with my newly learned lessons, but now need an additional column that denotes if the zip code is in the top 80% or the next 10% (80-90).

Also, I need the bottom 10% to sum the discharges and be represented in the last row instead of individual zip at that point.

With the Return feature, it seems to only be able to return the top 55%.

when I test 60% it returns all the records, because there are a lot of 1 counts for zip codes at the bottom. I suppose some sort of top-down cumulative count is needed and if expression to denote the zip codes.

One particular aspect that is needed is to include the last zip code in the top 80% to be the first one crossing into the second range. (Soif the cumulative fourth zip code is top 76% and the fifth adds 8%, it is still included in Top80%).

This is what I have so far.


WildCards in VB Code
Are there wildcards in VB? For example,

DoCmd.DeleteObject acQuery, [Zip]

But the program name that I am referencing is on the form, and I want to delete any query that is associated with that zip. The query will definitely have the zip in the name, but it may begin with query_, qry_, Q_, zip_Query, etc.

Is there a wildcard that I could use to isolate the zip from my form, regardless of what comes before or after it on the query?


Selecting the State Code
So I have a bunch of address that have:

Street Address City St Zip

Nothing is standardized accept the order. There could be a comma between city and state and there may or may not be a space between the state and zip. The zip also varies in size (#####, #####-####, or #########). Sometimes the zip is not there at all. I am looking for a way to identify the State or at least the position that is starts at. Any help would be awesome


Enter zip-code and town name will occur automatically
In a form I enter new contacts. Instead of typing the town name, when the zip code is allready entered, it would be easier, that the town name feild updated automatically. I have a table with zip-code and town name. How to do


Error 2950 when running setvalue macro
I have a form called 'CompanyInfo' . It contains a zip code, city, and state field. I have a seperate zip code table. On entry to the city field, I am running an embedded macro that sets the value of the city field to this:
DLookUp("[City]","[Zip Codes]","[Zip Code] =" & [Forms]![CompanyInfo]![Zip Code])

When I use the form and it enters the city field, I keep getting an error 2950. The database is trusted so that should not be the problem.


referencing a table to impute data
I have a table with of zip codes and it's associated city. I have a 2nd table with patient information. In my form, I would like to type in the zip code for a patient's address and cross reference the zip code table to automatically impute the city name. I've done this before, but can't replicate it.

As a side note, each patient has several addresses, so I would like the zip code finder to impute cities for each address.


Joined tables for query, but, not finding field if it starts with zero
I am stumped.I have a table with zip codes and installation cities in it and a table with addresses and zip codes.I joined the zip codes on the two tables and ran a query so the installation city will show up on the address table, but, Access ignores any zip codes that starts with a '0'. I already made sure the zip code field was text. It makes no difference whether I import the tables or link the tables to the original excel sheets. What am I missing


Combine fields into one
I have a form for taking orders and would like to combine [Customer Name], [Address], [City], [State], and [Zip Code] into one field so it can copy the complete name/address and then paste it into another application. The final combo field would have to be similar to how a normal address would be printed.

Customer Name
Address
City, State. Zip Code.

The name and address each on their own lines and city, state and zip code on one line with a coma after city


Visual Mapping Using Zip Codes?
I'm using an Access database to store the information we have on the leads from our marketing. One thing we collect is the zip code, which helps us know where our ad dollars are most converting.

The thing is, as a number it means very little. What I'd really like is to be able to somehow create a visualized report using the zip code data, creating either a cluster map or a heat map overlaid on a map of the US.

I assume Access doesn't have this capability itself, but I thought people might know of a solution that would integrate seamlessly with Access.


Get records by zip code one by one
Running a query that matches customers to resellers by zip code but i want to return only one match and eliminate other matches.

My title basically sums it up. I'm running a query that matches 50,000 + customers to about 10,000 resellers by exact zip code. Most of my customers come back matched with multiple resellers.

Is there a way where I can have access can match customers to ONLY one reseller without specifying any other criteria?


Dashes in phone numbers and zip codes
When creating reports and queries, Access has the random tendency to fail to mishandle placement of dashes in phone number fields and zip code fields. This occurs even though the master table data field was configured correctly.

A phone number like 555-555-5555 may (or may not) come out as 5555555555.
A zip+4 code may come out as 67502-5555 or 675025555.
A 4-digit zip may come out correctly like 67502, or it may come out as 67502- .

There seems to be no pattern to these occurrences, and I have had this occur when exporting data to Word or Excel.

This is especially annoying when doing a mail merge with several hundred records. The only way I know to correct it when it happens is to edit each letter or envelope, making needed individual corrections manually.

Is there a work-around that will eliminate this problem?


Extracting data with a query
I have a table (that is from a speadsheet imported from Excel) that has a zip code field. I need the zip code to appear as just 5 digits, but the data is imported as either '12345-1234' or '12345'. The single quotes are in the field.I do not want them there. I also have a different field for just the suffix. It's the same thing as I just stated, but I just need the last four digits of the zip code (when there is a suffix; if not, the field can be null). I was trying to do this in an update query, but whatever I'm doing, it's not working.


extract a file from .ZIP using VB or VBA
If there code available to extract one single file from a zip package and transfer it to a specific folder


Zipping a file from VBA
Can anybody point me in the right direction?

I am trying to zip a file that I have exported as a csv file from a form in access.

The file exports ok but the zipping is a problem.

I have used the code suggested by keiths and is listed below

Dim objzip
Set objzip = CreateObject("poles.zip")
objzip.Pack "c:/exporteddata/poles.csv", "c:/exporteddata/poles.zip"
Set objzip = Nothing

I hope I have interpreted it correctly, the error message I am getting is unable to create object.

I have followed the installation procedure for XStandard Zip component and It appears to have installed


Access is Acting Strange... Import excel into access table
I have some code that allows me to import an excel spreadsheet into a new access table. This has been working great for me for the last year, but today I have started getting strange results.

The code performs some basic maintenance on the excel sheet for formatting before importing the data. This has allows worked in the past, but now, the formatting is not sticking. I have stepped through the code and watched the changes take place, but once the data is in access, all the junk data that had been deleted is suddenly back and I can no longer use the data asI need it.

The import/formatting code I am using is:


counting zip codes as duplicates when first 5 digits are the same
I have this query:

SELECT * INTO KeepThese FROM final_output_summary_judgment AS f LEFT JOIN (SELECT t.fullName FROM final_output_summary_judgment AS t GROUP BY t.fullName HAVING Count(t.fullname)>=6) AS Ex ON f.fullName=Ex.fullName WHERE Ex.fullname Is Null;

Although it is supposed to remove any duplicates, it doesn't realize that where the 5 digit zip codes are the same that these should count as duplicates, but it doesn't count them as duplicates because they are not the same:

Schmidt,James
6800 NW 24 TER
FORT LAUDERDALE
FL
Schmidt,James
6800 NW 24 TER
FORT LAUDERDALE
FL

Is there a way to get it so that it keeps only one of these records only because the first five digits match of the zip code and all the other fields match. I would only want to preserve the record with the longer zip code in these types of situations