SQL Error within IIF Function ? 1

[accessSQL]

Hi life forms out there,

in an ACCESS mdb we had a SQL statement like this:

SELECT XAT.*, GWT.GwtText, XLS.XLSGWTNr, XLS.XLSNr, XLS.XLSLosnummer, XLS.XLSText, IIf([XATTyp]=1 Or [XATTyp]=2 Or [XATTyp]=7,[XATFormelergebnis]*[XATmittel],0) AS XATErgebnis, IIf(VarType([XATXBTNr])>1 And [XATXBTNr]<>99999999,'S','') AS ImStamm, IIf(VarType([XATNotiz])>1 And [XATXBTNr]<>99999999,'N','') AS NotizJN
FROM (XAT LEFT JOIN GWT ON XAT.XATGWTNr = GWT.GwtNr) LEFT JOIN XLS ON XAT.XATXLSNr = XLS.XLSNr
WHERE XAT.XATXOTNr = 11
ORDER BY XAT.XATGWTNr, XAT.XATPosNr

It doesn't work in our new ADP project now: &quot;Wrong syntax near '='.&quot;

Where is the error? It seems to be in the IIF Function.

Thanx,

Hansjoerg.

 

[cmmrfrds]

There is no IIF function in sql server. To replace the functionality use the CASE construct in sql server.

IIf([XATTyp]=1 Or [XATTyp]=2 Or [XATTyp]=7,[XATFormelergebnis]*[XATmittel],0) AS XATErgebnis

You will need to check the syntax.
(Case when xattyp = 1 or xattyp = 2 or xattyp = 7 then
[XATFormelergebnis]*[XATmittel] else 0) AS XATErgebnis