Topics Search

zip code formatting

zip code formatting
Views: 13
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:

Similar posts...


Zip + 4 to Zip 5 via LEFT, 5

Zip + 4 to Zip 5 via LEFT, 5 Icon
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

City, State, Zip Icon
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.

Make the tab order of one or two fields conditional on the value in a preceding field.

Make the tab order of one or two fields conditional on the value in a preceding field. Icon
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%

Query column return denoting records as top 80%, next 10%, & sums bottom 10% Icon
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

WildCards in VB Code Icon
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

Selecting the State Code Icon
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

Enter zip-code and town name will occur automatically Icon
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

Error 2950 when running setvalue macro Icon
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

referencing a table to impute data Icon
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

Joined tables for query, but, not finding field if it starts with zero Icon
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