Hi

I'm trying to set up a multiple if statement in a form field with the following criteria (based on ER issues). The aim is to determine if the KPI has been exceeded based on a number of days calculated in the [Duration] field

Where cmboER = Probation, Grievance or Capability
Where cmboGross = Gross Misconduct or Misconduct
Where Duration = time taken to complete (days)
Where KPI is either Y or N

So I'm looking to create a multiple if as follows:

If [cmboER] = "Probation" or "Grievance",IIF[Duration]>28,"Y"
if[cmboGross]="Misconduct",iif([Duration]>35,"Y"
if[cmboGross]="Gross Misconduct",iif([Duration]>48,"Y"
if[cmboER]="Capability",iif([Duration]>21,"Y"

else
"N"

I've tried putting this into a module but it doesn't work. Have also tried putting it in the field itself but again it doesn't work.

Is there a solution?

Thanks
Lynne

First question, are the bound fields of the combo box the actual text you indicate or is there a key field involved?

If you are doing this in a control on a form, you have to use the IIF() function not IF..THEN.. which is used in code.

As to the logic:

If any 1 of the 4 conditions are true, then you want to see a Y in the control, correct?

If all are false, you want to see a N in the control, correct?

Try:


I used a string result because based on your description you are returning a "Y" or "N" result. If you want, it's easy to convert the string result to a boolean result. Make the function return Boolean, the strResult variable should be a boolean ( bResult) and use False and True instead of "N" and "Y".

Hope this helps.

Mike

I have attached a database that offers a solution that does not use code. Basically I added two calculated controls that each evaluate a combo box + the duration. If both of these result in "N" then the kpi is "N" otherwise it is "Y"