Help with IIF function in report

[pixie]

Hello all

I wonder if anyone could help please. I am creating a database which calculates employee commissions due based on certain criteria in a report.

There are 3 targets and the commission due is dependant on which target is met, so for instance if target 1 is met, the commission per sale is X, if target 2 is met, the commission per sale is Y, and if target 3 is met, the commission per sale is Z.



So I have a number of calculated fields to determine qualifying sales and now I want to show the commission due. I am currently using the following expression (and variations of) which works fine, however I have to create this expression 3 times modified for each target:

=IIf([Target3MetT1]="Yes",[SumT1]*1.5,"0")

What I want is to have an expression like the following (which doesn't work) which only show the commission due for the highest target, 1, 2 or 3. Currently the above is expression is created 3 times, once for each target so shows the total due for each target but I only want to show the highest value as I am going to need to add them up at the end.

Any help appreciated and happy to answer questions, I am using Access 2007 on Windows 7 64bit

Thanks


=IIf([Target3MetT1]="Yes",[SumT1]*1.5)=IIf([Target2MetT1]="Yes",[SumT1]*1)=IIf([Target1MetT1]="Yes",[SumT1]*0.5,"0")

[rpeare]

If you're trying to put this in a single formula text box you need a nested if statement PLUS you want to consider how your IIF statement is scrolling through

In your case you've got 3 different targets so you've got to consider 3 different cases

target 1 2 and 3 are all yes in which case 1.5 is the multiplier
target 1 and 2 are yes, target 3 is not in which case 1 is the multiplier
target 1 is yes, target 2 and 3 are not, in which case the multiplier is .5

so you would have something like

iif([target3MetT1] = "Yes", 1.5,iif([Target2MetT1] = "Yes" and [Target2MetT1] = "No", 1, iif([TargetMet1T1] = "Yes" and [TargetMet2T1] = "No" and [TargetMet3T1] = "No", .5))) * [SumT1]

I may have some bracketing errors in there but you get the gist I hope.

[pixie]

Hello rpeare

Thank you for taking the time to respond to my question. Whilst I understand the logic, I am not great with syntax, I have tried to modify your code but I cannot get it to work. Perhaps if I write it simply you will be able to tell me the correct code?

If target 3 = yes x total by 1.5
or if target 3 = no and target 1 and 2 = yes x total by 1
or if target 3 and 2 = no and target 1 = yes x total by 0.5

Thanks again for your help

[pixie]

Hello again rpeare

I played a little more and got it to work! I think the issue (after correcting the field names) was just the = sign at the start, this is what has worked:

=IIf([Target3MetT1]="Yes",1.5,IIf([Target2MetT1]="Yes" And [Target3MetT1]="No",1,IIf([Target1MetT1]="Yes" And [Target2MetT1]="No" And [Target3MetT1]="No",0.5)))*[SumT1]

Thanks so much, you have saved me hours of pulling my hair out!!! Top answer

[rpeare]

Try this, I forgot to put in a value if you get something that comes through that doesn't meet any of the target checks:

iif([target3MetT1] = "Yes", 1.5,iif([Target2MetT1] = "Yes" and [Target2MetT1] = "No", 1, iif([TargetMet1T1] = "Yes" and [TargetMet2T1] = "No" and [TargetMet3T1] = "No", .5, 0))) * [SumT1]

Here is my original formula (if this is on a form or report it will have an = sign in front of it, if it's in a query it will have a Fieldname: in front of it)

iif([target3MetT1] = "Yes", 1.5, - The first part says if target 3 is met, use a multiplier value of 1.5
iif([Target2MetT1] = "Yes" and [Target2MetT1] = "No", 1, - The second part says if target 2 is met, but target 3 is not met, use a multiplier value of 1 (I assumed you could not meet targets 1 and 3 you could only meet them in order)
iif([TargetMet1T1] = "Yes" and [TargetMet2T1] = "No" and [TargetMet3T1] = "No", .5) - The third part says if target 1 is met and target 2 and target 3 are unmet use a multiplier value of .5

whatever the result of this nested if statement, multiply it by the value of [sumt1]

The formula is doing exactly what you're asking for (or should be).

Alternately you could do this:
Assuming you can not meet targets for 2 and 3 or 1 and 3 but to meet the target for the higher order you have to have met all the targets for the lower orders.

iif([targetmet1T1] + [TargetMet2T1] + [TargetMet3T1] = 3, 1.5, iif([targetmet1T1] + [TargetMet2T1] + [TargetMet3T1] = 2, 1, iif([targetmet1T1] + [TargetMet2T1] + [TargetMet3T1] = 1, .5, 0))) * [SumT1]

PLEASE NOTE: I am writing this without checking it so if it fails build your iif statement one block at a time and make sure it works before moving on to the next portion of the statement.