converting access 2007 update query to sql server 2005 update query

[Anthony97]

I ran this code on my access 07 db and it updates a number of records 61,425, I try and run the query in SQL Server 2005 modifying the IIf to CASE WHEN and I get a number of sytax errors.

Original code

UPDATE tblShipments
SET tblShipments.[Order Type] = IIf([EDI TP Id] Like "web*","WEB ",
IIf([FrctStndRte 1/0]=1,"EDI",IIf([EDI TP Id] Is Null,"MAN","EDI ")));

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'Like'.

I got that when I compiled it and it won't execute. Any suggestions on how I should modify the code to get the fields FrctStndRte 1/0 and EDI TP Id to update.

[ck9663]

Could you post the SQL Server version of your code?

--- CK

[Anthony97]

UPDATE tblShipments
SET tblShipments.[Order Type] = IIf([EDI TP Id] Like "web*","WEB ",
IIf([FrctStndRte 1/0]=1,"EDI",IIf([EDI TP Id] Is Null,"MAN","EDI ")));

[ck9663]

SQL Server does not have IIF. Use CASE..WHEN instead.

Search the syntax in BOL.

Good luck!!

--- CK