IIF question

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

Guest

Hi,
I have a table with an address field that I used a formula to trim down to
retrieve just ZIP codes. I have another table that lists all ZIP codes and
their associated cities. I want to be able to run a query that will convert
all of the zip codes in the first table into city names. Could you tell me
how to do this? I tried the following:
IIF([Expr5]=[ZipCode],[City],"")
as well as:
IIF("[Expr5]"="[ZipCode]","[City]","")
Note: Expr5 is the calculation from the first query that trimmed down
addresses to Zip codes. ZipCode and City are fields from the table that lists
all zips and cities.
If you know what I'm doing wrong please help,
Thanks
 
Maybe an IIF statement isn't the answer. I've used calculated columns before
using DLookup:
CityColumn: DLookup("City","TableWithCityAndZipInfo","ZipCode=" & Expr5)
Square brackets only necessary if there are spaces in field or table names.
 
Create a new query.

Add in the query that has the calculated zip code.
Add the table with the zip codes and cities
Join the two on the calculated zip code (expr5) and the zip code in the
table.
 
That sounds like a really good idea. However, I am now getting a "data
mismatch in criteria type" error. I think this is possibly due to something I
found about the Dlookup function which said that any field name in the
criteria has to be in the domain, but my Expr5 comes from a different table
than the City and ZipCode fields do. By the way, the
"TableWithCityAndZipInfo" table is called "ZIPList".
Any suggestions?
Thanks

rpurosky said:
Maybe an IIF statement isn't the answer. I've used calculated columns before
using DLookup:
CityColumn: DLookup("City","TableWithCityAndZipInfo","ZipCode=" & Expr5)
Square brackets only necessary if there are spaces in field or table names.

thatmoodieguy said:
Hi,
I have a table with an address field that I used a formula to trim down to
retrieve just ZIP codes. I have another table that lists all ZIP codes and
their associated cities. I want to be able to run a query that will convert
all of the zip codes in the first table into city names. Could you tell me
how to do this? I tried the following:
IIF([Expr5]=[ZipCode],[City],"")
as well as:
IIF("[Expr5]"="[ZipCode]","[City]","")
Note: Expr5 is the calculation from the first query that trimmed down
addresses to Zip codes. ZipCode and City are fields from the table that lists
all zips and cities.
If you know what I'm doing wrong please help,
Thanks
Click to expand...
Click to expand...
 
Back
Top