IIF function not returning the false value

[dfuas]

IIF function not returning the false value. I have the following results based on the query shown below.

SELECT Trade.Ref_No, Trade.CERS_Price, Market.[USD/EUR_Rate], IIf("CERS_Price_currency = US Dollar.USD",[Trade].[CERS_Price]*[Market].[USD/EUR_Rate] , 0) AS [Price In Euro], Market.[USD/CHF_Rate], Trade.CERS_Price_Currency
FROM Status INNER JOIN (Market INNER JOIN Trade ON Market.MarketDate = Trade.Trade_Date) ON Status.StatusID = Trade.StatusID;


Price In Euro USD/CHF_Rate CERS_Price_Currency CERS_Price
35 7 US Dollar. USD 5
91 7 Euro. EUR 13
1,715.00 7 Brazil Reais. BRL 245
759.00 33 Indian Rupees. INR 23.00


As you can see returns all calculations whether is USD or not.
Can you help please?
Ultimately I want to do this for all other currencies by nesting the IIF function.

Thanks

dfuas

 

[StevenS]

IIf("CERS_Price_currency = US Dollar.USD",[Trade].[CERS_Price]*[Market].[USD/EUR_Rate] , 0) AS [Price In Euro]

I think something is wrong with the quotes in the first part of the iif-statement
Maybe try something like
IIf(CERS_Price_currency = "USD",[Trade].[CERS_Price]*[Market].[USD/EUR_Rate] , 0) AS [Price In Euro]

HTH, Steven

 

[dfuas]

thanks a lot. It works now

dfuas