Full Version: Multiple Criteria If Statements
Lynne43
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

jzwp11
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?
MikeLyons
Try:
CODE
Function KPIExceeded() As String
Dim strResult As String

' Default to "N" and only set to "Y" when specific conditions are matched.
strResult = "N"

Select Case [cmboEr]
Case "Probation", "Grievance"
If [Duration]>28 Then strResult = "Y"
Case "Capability"
If [Duration] > 21 Then "Y"
End Select

If strResult = "N" Then ' No conditions matched so far
Select Case [cmboGross]
Case "Misconduct"
If [Duration]>35 Then strResult = "Y"
Case "Gross Misconduct"
If [Duration]>48 Then  strResult = "Y"
End Select
End If

KPIExceeded = strResult

End Function

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
jzwp11
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"