Access Query -> SQL Query

[TiGGi]

I have access query that I need convert to SQL. One of the fields has conditions that I do not know how to do on SQL side, please help!:

LotStatus: IIf([pub_jobPhase].[warraid]="reg05" And [pub_jobphase].[Model]<>"_LotSale","Builder Available",IIf(Not IsNull([pub_jobsales].[finalsaledate] Or ([pub_jobphase].[Model]="_LotSale")),"Closed",IIf([description]=" ","Available",IIf(([sales status]="spec" Or [sales status]="model" Or [sales status]="ssbto" Or [sales status]="ssip" Or (Not IsNull([description]) And IsNull([pub_jobsales].[finalsaledate]))),"Allocated")))) 

[Hack]

SQL Server does not support IIF

Replace that with CASE statements

[TiGGi]

I understand CASE, but how do I do multiple condition within one CASE:

if filed1 = '5' AND filed2 <> '' and field3 = '01/01/01' then 'whatever'
or
if filed1 = '5' or filed1 = '7' then 'whatever'

[audux]

try this one

case <your_fld>
when <fld_value> then <expectedoutput>
when <fld_value> then <expectedoutput>
else <output if conditions not met>
end as <your fld name>

hope it helps!