Update Query

[Guest]

I am trying to update a field called, CD2, in my table called, dmv01, which
is being done with an update query. I have it partially working. I want
the query to update CD2; however, it needs to update the records according to
the data that is located in the FIELD1 (which is in the same table...dmv01)
field. If the data in FIELD1 (is) LIKE “*NO RECORD*†then it should add
“999†in CD2 field. However, if the data in FIELD1 (is) LIKE “*CANCELLED*â€,
then it should add “888†in CD2 field. I have the first portion working, but
I don’t know how to configure the last part of it. I am enclosing my SQL
code below for you to review. Please help! Thanks.


UPDATE dmv01 SET dmv01.CD2 = "999"
WHERE (((dmv01.FIELD1) Like "*NO RECORD*"));

 

[Douglas J. Steele]

Easiest is to use two separate queries.

I suppose you could try something like:

UPDATE dmv01
SET CD2 = IIf(InStr([FIELD1], "NO RECORD") > 0, 999, 888)
WHERE FIELD1 LIKE "*NO RECORD*"
OR FIELD1 LIKE "*CANCELLED*"

but I don't see the advantage.