SQL Syntax Error (missing operator) in MS Access

Question

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.

Answer 1

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.

Answer 2

@Bedsheet and @Comforter are parameters that are based on user's input. It goes like this:

com.CommandText = "UPDATE Services SET Price = ..."

com.Parameters.AddWithValue("@Bedsheet", Val(TextBox2.Text))

com.Parameters.AddWithValue("@Comforter", Val(TextBox3.Text))

com.Parameters.AddWithValue("@PressOnly", Val(TextBox1.Text))

com.Parameters.AddWithValue("@WDF", Val(TextBox4.Text))

com.Parameters.AddWithValue("@WDP", Val(TextBox5.Text))

By the way, Switch() has done the job for me. Thank you :)