Related Topics

City State and Zip Tables

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.


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


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


Filtering a combo box based on data in previous field
I have 2 tables

customers & cities

In the cities table I have city, state & zip fields

I want to build a customers form that uses data from the cities table.

Here is what I want to be able to do.

Enter a city in 1st combobox (lets say springfield), tab to the state combo box and only have those states with a springfield listed (Mo, MA, IL, VA, OH), then tab to the 3rd box and in ithave the available zip codes for my city and state combo (lets say springfield ma) with 01101,01102,01103


Zipcode Lookup Autofill
I know similar questions has been asked before in the form but I cannot find a really good solution.

I worked with FileMaker a lot more from my previous job. My current job they use MS Access. I am trying to get familiar with Access but I am having troubles. In FM I was able to type a zip code and it would automatically populate the City and State. I have a data base with all of the city and states in access. How can I do the same thing in access like I was able to do in FM?

When I type the zip the city and state are automatically populated? Please I am VERY new at this and willing to learn


Combine City State, Zip into one display field
I have a field on my form that I need to combine my [city] [state] and [zip] fields into one field, BUT ALSO make that field editable. I already know how to simply combine those fields using =[city]&" "&[State]&", "&[Zip] in the control source, but although that displays everything in the correct format, I need to be able to edit it also.

So for example if the field were to display "New York NY, 10011" and I wanted to change it to "Manhattan NY, 10012" I could just type it into the field and it would update the apropriate fields.


Split field into 3
I have an address field that contains city, state and zip. I'm looking for a way to split this into 3 fields(city, state and zip).

RAHWAY , NJ 07065
FORT LAUDERDALE, FL 33309
ARMSTRONG , FL 32033
CLIFTON HEIGHTS, PA 19018

It's probably easy.but I can't seem to come up with anything that will work.


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.


Binding a Combo Box Search Result
I have admitted in threads in the past, that I am a rookie when it comes to access. But through all of your help, I have learned a great deal.

However, I have gotten myself stuck again.

I have a table with all the Zip codes and the City and States they belong to.

In my form, I have an unbound combo box search that finds the zip code and then in my Text Box, I have a formula in the Control Source that says =[Combo632].[column](2) It will show the city or state based on which column it is in.

My issue. I dont know how to bind those results. So that when I save the record, it saves the correct city and state in the form.


List style combo box issue
I'm new to Access and need some help with a simple task which is giving me serious trouble. I have built a table called customer details and it contains a field called city, and another field for state.

I want a user to be able to select a state from the stat combo box that I've created and have all the cities in the selected state appear in the city combo box.
At the moment I created 2 tables (one for city and one for state) and I have every city and state packed into those tables, so when I click the city combo box, all the cities in the countryare listed, but I want only the cities in the selected state to appear instead.


Is it possible?
I want to create a macro that will export a single query to excel, that can create multiple workbooks from a list. It is not realistic to create individual queries for each item (100+ items and changes often) and need each item in a list in a different worksheet (preferably in the same workbook). I don't really care if each worksheet is not labeled. The list will be in one query and the primary information in another.

Example

Area List: 1,2,3,4,5,6,etc

Information table: Area, Store, City, State, Zip

Output for each worksheet:
Area 1 Store 2 City New Haven State VT
Area 1 Store 3 City Johnassaburg State VT

Next worksheet:
Area 2 Store 10 City Fort Worth State TX
Area 3 Store 12 City Dallas State TX


Sort by state with the blank fields at the end of the report
Have a db (access) with all the usual profile fields such as last, first, address, city, state, zip and deceased. Some fields will have no data like address, city, state, zip and deceased.Would like to sort by state with the blank fields at the end of the report.

This is as close as I've been able to get:


City, State Zip lookup
I want to be able to fill in the City, State and Zip Code fields in my customer and employee table from my zipcode table.

I see how I can do a multiple lookup from the zipcode table, however, I don't see how I can use the three columns of data from zipcode table to complete and fill in the same three fields in the customer or employee table.

As a note, I'm constructing a web data base that will be hosted on a sharepoint hosting site.

Here are my fields in both tables:

zipcode table:
ID
zip_code
city
state_prefix
county
locationtext
location

customer table:
first_name
last_name
address1
address2
city
state_prefix
zip_code
home_phone
mobile_phone

Can I use the Lookup & Relationship wizard (I suspect not) or do I need to construct a macro from a query, etc


combining columns into 1 access
I need to take 3 columns from 1 table and combine into 1 column on another table. Example City column-State column-Zip column to Address 3 column which is City, State, Zip. Does anyoneknow how to do this in a update query in Access?


Multiple Same Fields in Access Forms
I hope this isn't too wordy.

I have a form that I am trying to create. It only has three columns on it, but I need to be able to recognize 2 of the 3 fields until I change it. Below is an example of what I want to be able to do on the form.

State: Tennessee
City: Memphis
Zip Code: 38111
38112
38114
38115
and so on with adding the zip codes, until I get to a new City, and eventually a new State. I don't want to have to continue to type Tennessee and Memphis, but I can't set those fields (like a date field), because they will have to change at some point.


Report contains address with blank lines
I'm sure this might have been answered before but I can't find the answer.

I have a report with the following fields

Name
Address 1
Address 2
Address 3
City, State ZIP

When there is not address 3 and the report prints it is leaving a space between address 2 and City, State ZIP

Is there a way to remove this space when there is no Line 3 or even when there is no Line


Zipcodes Autofill in Contacts Suppliers Customers City State
I have a Zipcode database which I imported in the Northwind database Access template. I have been looking for a solution where in forms such as "Suppliers" "Customers" "Users" the contact information forms to fill the City and State when user inputs the zip code in "Zip Code/Postal Zone" box. Being a newbie at the Access your valuable help is


How to use code in button to check relationship integrity?
Suppose I have two tables: State, City

Related to the relationship "one-to-many."

They also have enabled:

Enforce referential Integrity and Cascade Update Related Fields

Thus, it is possible to change the name of the state or city, but can not be deleted until the state is associated with some of the city.

I also made a form for the "state" in which is the list that contains a list of all states. The name of the list is "lstState". So that I can delete the "State" I make a button in form and I use the following code:

[code]

Everything works fine when the state is not assigned to any one city. But the problem arises when trying to delete a state which is assigned to the city, that is when I select this stateand click on the delete button then the state is not deleted - this is ok, but without any message told why the state is not deleted and that's the problem. My question is how to make the code that the user receives a message that such State can not be deleted because there are cities that are associated with it.


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


Country Code Calculation
I am working on the address portion of my database now.

I have a listing of all USA States, Canadian Provinces/Territories, and Mexican State Abbreviations from http://www.iowa.gov/tax/forms/84055.pdf

When a user is completing this particular form, they will enter the city, state, and zip. (I know I can get zip codes too, but our organization lacks the funds and resources to maintain an up to date listing at all times for that option to be practical) When they select the state/province abbreviation from a lookup box, I would like a calculated field to associate the appropriate three digit country code (aka, USA, CAN, or MEX).

I am not real experienced in calculated fields, any pointers?