IF Statement extracting via MS Query from SQL to Excel

[MICHELLEBEZUIDENHOUT]

Morning,

I am struggling with the IF function in my MS Query back to Excel.

IIF([Currency]='R',[ValueOutsInclDiscZAR],([ValueOutsInclDiscZAR]*13.60))

I get the following error:
Incorrect syntax near '='

Please assist me with this error.

Thanks
Michelle

 

[Fazza]

What you've posted looks prefect to me, Michelle - though I don't work with SQL Server.
Is there any chance it has a different delimiter for text? So maybe [Currency] = "R"
Hopefully someone can advise the syntax issue.

Some ideas for workarounds follow.

If you're loading the data to a worksheet, return the currency field as well as ValueOutsInclDiscZAR field and use a new field in the worksheet to do the conversion. Field can auto-populate on query refresh.

If you're loading the data to a recordset, make it disconnected then loop through and change as required.

If you have a recordset, load that to an array, then loop through & change as required.

A little trickier might be to make a table of FX rates in Excel or your database, and join to that table as well in your query. So the query itself does the conversion.

 

[MICHELLEBEZUIDENHOUT]

Thanks Fazza

Will play around.

 

[Fazza]

hi, Michelle

another workaround, maybe. less elegant. do the query in two parts to avoid the IIF. something like below. regards

SELECT ZAR
FROM table
WHERE [Currency] = 'R'
UNION
SELECT ZAR * 13.6
FROM table
WHERE [Currency] <> 'R'

 

[MICHELLEBEZUIDENHOUT]

Hi Fazza

Here is the current version before the above note:
SELECT SorDetail.MStockCode AS 'Item', SorDetail.MStockDes AS 'Stock Description', SorMaster.OrderType, SorMaster.SalesOrder, SorMaster.Customer, SorMaster.CustomerName, SorMaster.Salesperson AS 'Rep', SorMaster.CustomerPoNumber, SorMaster.OrderDate, SorDetail.MShipQty, MBackOrderQty+MShipQty AS 'OutstQty', SorDetail.MWarehouse, SorMaster.ReqShipDate AS 'ClientExpDate', ((MBackOrderQty+MShipQty)*MPrice)*((1-(MDiscPct1/100))-MDiscValue) AS 'ValueOutsInclDiscZAR', SorMaster.Currency AS 'Currency'
FROM SysproCompanyP.dbo.SorDetail SorDetail, SysproCompanyP.dbo.SorMaster SorMaster
WHERE SorMaster.SalesOrder = SorDetail.SalesOrder AND ((MShipQty+MBackOrderQty>$.000) AND (SorMaster.OrderStatus<>'\' And SorMaster.OrderStatus<>'*' And SorMaster.OrderStatus<>'9'))

How do I bring in your query into the above query?
The value is in AS 'ValueOutsInclDiscZAR' column and the Currency is in the AS 'Currency' column

Thanks
Michelle

 

[MICHELLEBEZUIDENHOUT]

Hi Fazza

Ok I see the currency comes from SorMaster.Currency
We linked the MPrice from another table SorDetail.MPrice

How I do that IIF then?

Thanks
Michelle

 

[Fazza]

hello, Michelle

the SQL FROM clause refers to two tables only - SorDetail and SorMaster

using tables not in the FROM clause won't work
when in the FROM clause the relationship between the tables is defined
though it is likely simple enough. as I don't know the relationship between the tables, I can't advise on this (SQL)

it needs understanding of the relationships between the tables to get the SQL correct

a tiny sample of relevant tables might be best