Salima has attended:
Access Intermediate course
Excel VBA Intro Intermediate course
Access => IIF Function
Hiya,
I am trying to run an IIF statement question, however Access seems to ignore the second part of the IIF statement and return results for the first part of the argument only.
I expect to see two different results depending on whether the Currency is 'GBP' or 'USD'
CCY_Port_Value: IIf("dbo_CURRENCIES.BRIEF_DESC_ENG='GBP'",Sum(DSum("DEAL_AMOUNT","QRY_RESIDUAL_VALUE_TABLE"," dbo_CURRENCIES.BRIEF_DESC_ENG='GBP'")),IIf("dbo_CURRENCIES.BRIEF_DESC_ENG='USD'",Sum(DSum("DEAL_AMOUNT","QRY_RESIDUAL_VALUE_TABLE"," dbo_CURRENCIES.BRIEF_DESC_ENG='USD'"))))
I look forward to your response.
Many Thanks
Salima
RE: Access => IIF Function
Hi Salima,
Thank you for your question.
I will consult with a colleague and if we find a solution I will email you today before close of play.
Regards
Simon
RE: Access => IIF Function
Hi Salima
Thanks for your question
Can you clarify what happens when the currency is USD, the second part of your argument. Does the function return nothing, or does it return the value for GBP instead
Thanks
Stephen
RE: Access => IIF Function
Hi Stephen,
It returns the GBP value instead, it's almost as it is disregards the second argument of the IIF function, if I switch the arguments around then the formula returns the USD value irrespective of currency.
Cheers
Salima
RE: Access => IIF Function
Hi Salima
If you only have the two currencies, GBP and USD (as appears to be the case) then you do not require a second IIF statement as both contingencies are covered in the truepart and falsepart arguments of the first IIF statement. Without seeing your database it is difficult to be precise, but I suggest something alomng the lines of
CCY_Port_Value: IIf("dbo_CURRENCIES.BRIEF_DESC_ENG='GBP'",Sum(DSum("DEAL_AMOUNT","QRY_RESIDUAL_VALUE_TABLE"," dbo_CURRENCIES.BRIEF_DESC_ENG='GBP'"),Sum(DSum("DEAL_AMOUNT","QRY_RESIDUAL_VALUE_TABLE"," dbo_CURRENCIES.BRIEF_DESC_ENG='USD'"))
So the truepart adds up values where the currency is GDP, and the falsepart values where it is not GDP, i.e USD.
If there are in fact other currencies, what are they and what results do you want returning for them
Regards
Stephen
