Using the iif function and or operator in an update query

  • Thread starter Thread starter Miquel Korah
  • Start date Start date
M

Miquel Korah

Hi here's what I'm trying to do:

I have the following statement
IIf([Products] Like "*meat*" Or "*jerk*" Or "*lamb*" Or "*ham*" O
"*bacon*" Or "*egg*" Or "*chick*" Or "*deer*" Or "*feather*" Or "*cat*
Or "*brisk*" Or "*beef*" Or "*salami*" Or "*sausage*" Or "*pork*" O
"*animal*","animal product",[Products])

In the "Update to" section of the design grid of an update query. Whe
I run it it updates all the fields in the Products table to "anima
products" and does not pick out the text in the Like statement.

Would appreciate some help.

Thanks Mique
 
Hi here's what I'm trying to do:

I have the following statement
IIf([Products] Like "*meat*" Or "*jerk*" Or "*lamb*" Or "*ham*" Or
"*bacon*" Or "*egg*" Or "*chick*" Or "*deer*" Or "*feather*" Or "*cat*"
Or "*brisk*" Or "*beef*" Or "*salami*" Or "*sausage*" Or "*pork*" Or
"*animal*","animal product",[Products])

In the "Update to" section of the design grid of an update query. When
I run it it updates all the fields in the Products table to "animal
products" and does not pick out the text in the Like statement.

Would appreciate some help.

Thanks Miquel
Click to expand...
Miguel,
I don't remember what the limit is on how many "OR's" you can have in
an IIF statement, but at the very least you MUST reiterate the
criteria field with each Or statement:

IIf([Products] Like "*meat*" Or [Products] like "*jerk*" Or [Products]
like "*lamb*" Or ... etc.
 
I would add a field in the products table that identifies if the product is
an "Animal Product". In my opinion, creating an IIf() expression like this
would be totally un-manageable.
 
Hi here's what I'm trying to do:

I have the following statement
IIf([Products] Like "*meat*" Or "*jerk*" Or "*lamb*" Or "*ham*" Or
"*bacon*" Or "*egg*" Or "*chick*" Or "*deer*" Or "*feather*" Or "*cat*"
Or "*brisk*" Or "*beef*" Or "*salami*" Or "*sausage*" Or "*pork*" Or
"*animal*","animal product",[Products])

In the "Update to" section of the design grid of an update query. When
I run it it updates all the fields in the Products table to "animal
products" and does not pick out the text in the Like statement.

Would appreciate some help.
Click to expand...

The correct syntax would be to repeat the [Products] LIKE clause for
every one of the products: e.g.

IIF([Products] LIKE "*meat*" OR [Products] LIKE "*jerk*" OR [Products]
LIKE ...

The OR operator is a logical mathematical operator, not an English
language conjunction: it is used to combine expressions which are
either TRUE or FALSE and return TRUE if either expression is TRUE. The
expression "*jerk*" is actually true - since it's nonzero; so all rows
will be true in this case.

You might try changing the IIF using the repeated [Products] LIKE
clause, but this may exceed the allowed size of an expression. An
alternative would be to create a small table with one text field, with
each of these expressions as a value. You could then use an update
query joining your table to this AnimalProducts table:

UPDATE yourtable
INNER JOIN AnimalProducts
ON yourtable.Products LIKE "*" & AnimalProducts.Product & "*"
SET yourtable.fieldname = "Animal Products";
 
Back
Top