Nested IIf, I think..

[fibayne]

Hi

Sorry if this is the wrong forum to post to as it is a query, wasnt quite sure as the result of the query is in a form

I am trying to do a calculation based on the result on field [Deal], I can get it to work if I have on one IIf expression ie

Expr1: IIf(([Deal])='CPA',[Quantity]*10)

I tried this but cant get it to work

Expr1: IIf(([Deal])='CPA',[Quantity]*10), IIf(([Deal])='CPM',[Quantity]*100), IIf(([Deal])='CPA',[Quantity]*1000)

Once again any help wouldbe greatly appreciated

Fi

 

[Brianwarnock]

The syntax is incorrect

Expr1: IIf([Deal]='CPA',[Quantity]*10,IIf([Deal]='CPM',[Quantity]*100,IIf([Deal]='CPA',[Quantity]*1000)))

plus I don't see how it ever gets to the3rd test.

Brian

 

[fibayne]

Hi Brian

Expr1: IIf([Deal]='CPA',[Quantity]*10,
IIf([Deal]='CPM',[Quantity]*100,
IIf([Deal]='CPA',[Quantity]*1000)))

In the meantime I came up with this and it seems to be working, will try yours and see it that gives the same result, many thanks for your help

Fi

Expr2: IIf(([Deal])='CPA',
IIf(([Deal])='CPC',
IIf(([Deal])='CPM',
[Quantity]*[CostPrice]),
[Quantity]*[CostPrice]),
[Quantity]/1000*[CostPrice])

 

[Brianwarnock]

You have two tests for CPA and none for CPC in the original.

I suspect your working solution would be difficult to follow and change later, remember that the IIF is IIF(test,Trueaction,Falseaction), when nesting the next IIf is the false action, its simple but you have to watch the bracketing and any final all false scenario.

Brian