if then

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

ACCESS 2003

I need to put three or more "if, then" in one expression:
If "city"="wa" then "Watsonville"
If city ="HO" then "hollister"
etc....

Also sometimes I have a code (say 50 state abbreviations) and need to return
the state spelled out in full. Can i do a table and lookup :

if "CA" then look in list for match to "CA" and return California
 
You should have a table of city codes that has fields for the code and the
city name. Then, when you add another code, you don't ever have to modify
this "if" expression. State codes and names should be set up the same.

This is basically the same as the product ID and product name in
Northwind.mdb.
 
Ok:
A. So I create a table with city code and city. I am not sure how I
tell the other table to match/lookup the code and then have a result that =
"City spelled out." And do I have to create another field or can I do it in
the "city code" field?

B: What about:
multiple "if..then" without doing another table?
 
A. Always expose records through forms and reports. This allows you to store
the city code and display the city name using a combo box.

B. If you don't want to use a table then create a simple user defined
function. Create a new blank module and paste this code into it. Save the
module as "basGetFunctions".

Function GetCityFromCode(strCityCode as String) as String
Select Case strCityCode
Case "WA"
GetCityFromCode = "Watsonville"
Case "HO"
GetCityFromCode = "Hollister"
Case "EC"
GetCityFromCode = "Eau Claire"
Case Else
GetCityFromCode = "Unknown"
End Select
End Function

You can then use this function in a query or control source where ever you
might have used an IIf().
 
Back
Top