IF/THEN STATEMENTS 1

[3outta5]

I have a database where the address lists all normal address properties. I need to be able to automatically add the "County" to the database.

First Choice: When a city is displayed in a city column cell the county column cell would automatically insert the proper county.

Second Choice: A macro that would accomplish same.

I am not knowledgeable in SQL and know very little about Visual Basic. So, if the answer involves either of those methods - please be explicit.

I thank you in advance for any help I can get.

 

[Scott2S]

I need to understand a few more things. Are you talking about two text box controls (city and county) on one form? Are both of these controls bound to a field in the record source or is the county an unbound control?

One last question, is there a table somewhere in your database that you can query against to find out what county a city is in? If not, how are you determinging the county?

scott

 

[3outta5]

I have been given an Excel Spreadsheet containing a list with all of the information except the county information and (I didn't mention previously) the judicial district that each city is in.

This is a dynamic list; meaning there is and always will be new names added from an Excel spreadsheet.

So, my task is two-fold. First to take the existing list and assign the county and judicial district to each record. Secondly, to make it relatively automatic to assign the county and judicial district to each new record added.

To accomplish the first part I think I will leave it in Excel and sort by cities and past in the information. A little tedious, but not overwhelming.

My real problem is not knowing how to create an If/Then scenario with so many variables i.e. assigning 300+ cities with county and judicial district information. So as each new record is appended, the county and judicial district information will be added automatically. I am not sure how to accomplish this!

I have taken an introductory course on Visual Basic and have done some If/Then, If/Else and Case/Select programming, but I don't know how to tie that into Access.

So, to answer your questions. So far all information is in a single table. I hvae not built the form yet. This is all a new project. So, if you have any tips that I can benefit from, I would certainly appreciate the help.

 

[JerryDennison]

Personallay, I would not use Excel as a database. It does do some db type functions well but what you're describing calls for a true relational database. As for assigning districts and cities, the info must come from somewhere. You would need to setup some type of method for crossreferencing this info. Possibly one or more tables for defining the county and all jurisdictions within it. This may be available in database format from the state government.

 

[jfhewitt]

Not hard. Create a separate lookup table. Three fields: city, county, judicial district. Make city the key field. Put 2 unbound fields on your form...one for county, one for judicial district. use an exprression with the dlookup function in the county and judicial district fields. Read help section on dlookup to find out how to do it. Piece of cake. Other method is to use a query linked to the key field. Link the new table to your base table on the "city" field...creat a query linking both tables, and base your form on the query rather than the base field. You may have to creat 2 dummy dbs to get the feel for these procedures.