Syntax Error (missing operator) in Access Query

Question

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

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?

There is no CASE operator in JetSQL(ms access) change it with IIf function. — 4dmonster
– 4dmonster, Commented May 14, 2014 at 7:55

EliVictorio · Accepted Answer · 2014-05-14 09:51:16Z

1

Quoting from a person who gave me help, "The CASE syntax works in SQL/Server but is not supported in ACE (the Access native SQL driver)." I used the Switch() function instead and got the job done. here is my new code:

UPDATE Services SET Price = SWITCH(ID = 'Bedsheet', @Bedsheet, ID = 'Comforter', @Comforter, ID = 'PressOnly', @PressOnly, ID = 'WDF', @WDF, ID = 'WDP', @WDP) 
WHERE ID IN('Bedsheet','Comforter','PressOnly','WDF','WDP')

answered May 14, 2014 at 9:51

EliVictorio

111 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Syntax Error (missing operator) in Access Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related