CASE statement in UPDATE query

[Steve]

I am trying to do the following query in Access 2000 and
cannot get it to work. Anybody tell me how to get this to
work? Thanks.

UPDATE trans
SET category =
CASE
WHEN (description LIKE "*KROGER*") THEN "Grocery"
WHEN (description LIKE "*ALBERTSON*") THEN "Grocery"
WHEN (description LIKE "*IHOP*") THEN "Dining Out"
WHEN (description LIKE "*WALGREEN*") THEN "Medical"
WHEN (description LIKE "*WELLS FARGO*")
THEN "Mortgage"
ELSE "Miscellaneous"
END;

 

[Gerald Stanley]

Try something along the lines of
UPDATE trans
SET category = IIf(description LIKE '*KROGER*' , 'Grocery'
, IIf(description LIKE '*ALBERTSON*' , 'Grocery' ,
IIf(description LIKE '*IHOP*' , 'Dining Out' ,
IIf(description LIKE '*WALGREEN*' , 'Medical' ,
IIf(description LIKE '*WELLS FARGO*' , 'Mortgage' ,
'Miscellaneous' )))))

Hope This Helps
Gerald Stanley MCSD

 

[John Vinson]

I am trying to do the following query in Access 2000 and
cannot get it to work. Anybody tell me how to get this to
work? Thanks.

UPDATE trans
SET category =
CASE
WHEN (description LIKE "*KROGER*") THEN "Grocery"
WHEN (description LIKE "*ALBERTSON*") THEN "Grocery"
WHEN (description LIKE "*IHOP*") THEN "Dining Out"
WHEN (description LIKE "*WALGREEN*") THEN "Medical"
WHEN (description LIKE "*WELLS FARGO*")
THEN "Mortgage"
ELSE "Miscellaneous"
END;

The CASE operator is available in SQL/Server but not in Access. Try
using the Switch function instead; it takes operands in pairs, and
returns the second member of the first pair for which the first
argument is true:

UPDATE trans
SET category =
SWITCH(description LIKE "*KROGER*", "Grocery",
description LIKE "*ALBERTSON*", "Grocery",
description LIKE "*IHOP*", "Dining Out",
description LIKE "*WALGREEN*", "Medical",
description LIKE "*WELLS FARGO*", "Mortgage",
TRUE, "Miscellaneous")