SQL Syntax Error (missing operator) in MS Access

I have a table named Services and it has two columns ID and Price. here is my code:

com.CommandText = "UPDATE Services SET Price = (CASE WHEN (ID = 'Bedsheet') THEN @Bedsheet WHEN (ID = 'Comforter') THEN @Comforter WHEN (ID = 'PressOnly') THEN @PressOnly WHEN (ID = 'WDF') THEN @WDF WHEN (ID = 'WDP') THEN @WDP END) WHERE ID IN('Bedsheet','Comforter','PressOnly','WDF','WDP')"

It always says a Syntax error (missing operator) in query expression message. What do I have to correct in my code? Thanks in advance.

 

Question Info


Last updated February 12, 2018 Views 490 Applies to:
Answer
Answer

The CASE syntax works in SQL/Server but is not supported in ACE (the Access native SQL driver). I'd suggest using the Switch() function instead; see the VBA help. Where are @Bedsheet and @Comforter defined?

Alternatively (and preferably) you could consider using a Prices table with fields PriceID (autonumber primary key), Item (text, e.g. "Comforter" or "PressOnly"), and ItemPrice (Currency); you could then simply have a Join on Item to look up the price for that item.

John W. Vinson/MVP

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.