Dec 22 2011, 11:54 AM
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"
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?
Dec 22 2011, 12:19 PM
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?
Dec 22 2011, 12:31 PM
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"
If [Duration] > 21 Then "Y"
If strResult = "N" Then ' No conditions matched so far
Select Case [cmboGross]
If [Duration]>35 Then strResult = "Y"
Case "Gross Misconduct"
If [Duration]>48 Then strResult = "Y"
KPIExceeded = strResult
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.
Dec 22 2011, 12:46 PM
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"
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here