Hi all,
I am trying to write an IIF statement in a query to get the result as shown in the attached excel.
Please somebody help!!!!!
TIA,
Mismag
Hi all,
I am trying to write an IIF statement in a query to get the result as shown in the attached excel.
Please somebody help!!!!!
TIA,
Mismag
It's unclear given the data presented what [Gap Deals], [Lease Protect Deals], [Service Contract Deals], and [Lease Deals] are.Code:IIF( ProductName = "Gap", [Gap Deals] / [Service contract delas], IIF( ProductName = "Lease Protect", [Lease protect Deals] / [Lease Deals], Deals / TotalDeals ) )
Oh wait, I think I understand now. Try this (replace TABLE_NAME with the name of your table):
Code:IIF( ProductName = "Gap", Deals / DLookup("Deals", "TABLE_NAME", "ProductName='Service Contracts'"), IIF( ProductName = "Lease Protect", Deals / DLookup("Deals", "TABLE_NAME", "ProductName='Leased'"), Deals / TotalDeals ) )
This formula doesn't throw any errors but the Gap and Lease Protect Penetration % values are not calculating correctly.Oh wait, I think I understand now. Try this (replace TABLE_NAME with the name of your table):
Code:IIF( ProductName = "Gap", Deals / DLookup("Deals", "TABLE_NAME", "ProductName='Service Contracts'"), IIF( ProductName = "Lease Protect", Deals / DLookup("Deals", "TABLE_NAME", "ProductName='Leased'"), Deals / TotalDeals ) )
Please see the attachment.
Any help is greatly appreciated.
TIA,
Mismag