Syntex Error, Update Multi - colomns - multi feilds in one query .

[Scorp Scorp]

Dear All,
Hope every thing going fine...

Am trying to run this query , but am getting :
Syntex Erro (missing operator) in query expression 'CASE code .....

My querys is :

Update tb_details
set quantity = CASE code 
when 8668 then "2"
when 8669 then "3"
when 8670 then "0" 
when 8671 then "2"
END
where 
code   IN (8668, 8669, 8670, 8671) ;
 

where Qunatity is a string value and code is an automatic number .
Access 2007, Sql view

P.S.: i aslo tried for feild other than the code feild , since it could be a Key-word, error still exist
Any Ideas ...
Thnaks in advance

[jimatqsi]

Is this in the right forum? Looks like maybe it should be in the SQL forum.

jim

[gershwyn]

Access does not support the SQL you are trying to use here. The closest equivalent would be to use the Switch function.

SET quantity = Switch(Code = 8668, "2", Code = 8669, "3")

Though personally, I'd prefer to create a table that cross references the code to the quantity, and use a join to lookup the values. That way any changes or additions could be made directly to the table without increasing the complexity of the SQL statement.

[Scorp Scorp]

Hey gershwyn thanks for the note Access does not support the SQL you are trying to use here. I didnt know !!!

I'll try to use your statmnt ...

Thanks .

[Scorp Scorp]

Thanks gershwyn , your statmnt worked just fine, did what it supposed to do :) , thanks a lot

Regards,