Hi,

trying to do an update with a case statement... but I can't seem to get the syntax right... when I try the following in Access, I get the error: 'Syntax error (missing operator) in query expression 'CASE WHEN (OrderType = 'NEW') THEN "Slippage" ELSE "Churn" END'


Query:
UPDATE testTable SET CostSplit = CASE
  WHEN (OrderType = 'NEW') THEN "Slippage"
  ELSE "Churn"
END

UPDATE testTable SET CostSplit = iif(OrderType = "NEW","Slippage","Churn")

I think in access CASE Is not available, so use IIF function.


urt

Help yourself by helping someone.

Thank you for the IIF syntax... but what happens if I need to check more than 1 thing? How do you add more if's? LIke if a AND b AND c = 'option1' ELSE = 'option2'?

UPDATE testTable SET CostSplit = iif(OrderType = "NEW" and somefield="somevalue","Slippage","Churn")

urt

Help yourself by helping someone.