98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Access Training and help » Access => IIF Function
Access => IIF Function
Resolved · Urgent Priority · Version 2007
Wed 17 Nov 2010, 13:52Delegate Salima said...
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
Thu 18 Nov 2010, 11:20Trainer Simon said...
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
Mon 29 Nov 2010, 14:18Trainer Stephen said...
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
Mon 29 Nov 2010, 14:21Delegate Salima said...
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
Tue 30 Nov 2010, 10:42Trainer Stephen said...
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
Mon 6 Dec 2010: Automatically marked as resolved.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Access tip:Splittng a databaseSplit your database into two (at least). |
