If Statements in Microsoft Query

[bluelabel]

Hi,

Could someone please help me with the correct way to use an If statement to pull data into Excel via Microsoft Query (ODBC connection)

I need if a certain column is a number 2, then convert the result to a negative, if not then normal result

SELECT invoicedetailproduct.internaldocnum, IIF(invoiceheader.doctype=2, invoicedetailproduct.taxableamt*-1, invoicedetailproduct.taxableamt)
FROM admin.invoicedetailproduct invoicedetailproduct, admin.invoiceheader invoiceheader
WHERE invoiceheader.internaldocnum = invoicedetailproduct.internaldocnum

No matter which way I do the formula I end up with a Syntax Error, so I'm obviously missing something.

Thanks

 

[jimrward]

where are you pulling the data from what is the source database and type

 

[bluelabel]

where are you pulling the data from what is the source database and type

From the Accounting system. SQL based system with an ODBC connection. I can't get a direct SQL link hence the ODBC.

 

[jimrward]

what is the name of the accounting system it may well have sql but depends on vintage and vendor I believe iif is a recent addition to sql 2012 and behaves in the same way as CASE again you may find this variant of sql does not support CASE

 

[John_w]

Your SQL looks correct. Maybe missing spaces, before FROM and WHERE.

SELECT invoicedetailproduct.internaldocnum, IIF(invoiceheader.doctype=2, invoicedetailproduct.taxableamt*-1, invoicedetailproduct.taxableamt)
 FROM admin.invoicedetailproduct invoicedetailproduct, admin.invoiceheader invoiceheader
 WHERE invoiceheader.internaldocnum = invoicedetailproduct.internaldocnum