Too many IIFs

[StephanieH]

I want to add a field to my query that displays "Approved" if the [Note]
field contains certain verbiage and "Review" for anything else.
IIf([Note] Like "*Cost*","Approved","Review") works except that I want to
add several more conditions. I thought about setting up a second table that
would list the approved verbiage in an Approved field. Is it possible to
change the "*Cost*" part above to point to a field in another table. To have
it display "Approved" anytime a word in the other table's Approved field is
found within the field [Note]?

 

[Matthias Klaey]

I want to add a field to my query that displays "Approved" if the [Note]
field contains certain verbiage and "Review" for anything else.
IIf([Note] Like "*Cost*","Approved","Review") works except that I want to
add several more conditions. I thought about setting up a second table that
would list the approved verbiage in an Approved field. Is it possible to
change the "*Cost*" part above to point to a field in another table. To have
it display "Approved" anytime a word in the other table's Approved field is
found within the field [Note]?

I would not rely on the text in the note field. For example, all of
the following words and many others would return "Approved" according
to your criterion:

cost
Costa
costing
costal
costar
costermonger
costive
costume
Costa Rica
Costa-Gavras
Costello
Costner Kevin
costocervicalis

I would give the person writing the note text the right to mark a
checkbox as "Approved".

HTH
Matthias Kläy

 

[KARL DEWEY]

If you have a lots of if's then use what I call a translation table. In this
table have two fields - what you are looking for and what the output is to be.
XlatIn XlatOut
Cost Approved
Bill Approved
Cut Approved
Wait Review
Unknown -- Null in XlatIn field.

Put both of your tables in the query design view grid but do not join.
Place [XlatOut] in the output row with whatever alias you want.
Right_Now:[XlatOut]
In the criteria row under [Note] put
Like "*" & [XlatIn] & "*" OR Null