We help IT Professionals succeed at work.

Check two different conditions in one Function

I have the attached function, but what I need is for it to loop through the first condtion and if false, then loop through teh next condition and if that is false, return back the value of the dScore.  So, am a little perplexed as to how to tell it to check the first and if false, check the second, different condition?
Public Function fntCheckEligibilty(ByVal RepID As Integer, ByVal EvalYear As Integer, _
                                 ByVal EvalQuarter As Integer, dScore As Double) As Double
'Checks the hire date and if it is in the quarter, sets the IncGoalResult to 0
'as by business rules, the employee is not eligible.  Also checks the status of the employee to see
'if they have been manually marked as ineligible by Weston
On Error GoTo ErrorHandler
Dim strSelect As String
Dim strIn   As String
Dim strIneligible As String
Dim rst As Recordset

Select Case EvalQuarter
    Case 1
        strIn = "(1,2,3)"
    Case 2
        strIn = "(4,5,6)"
    Case 3
        strIn = "(7,8,9)"
    Case 4
        strIn = "(10,11,12)"
End Select

strSelect = "SELECT Representative.ID AS RepID, TblEmployees.EmpHREmployeeNumber, Year([HRDateHired]) AS HireYear, " & _
    "Month([HRDateHired]) AS HireMonth " & _
    "FROM (TblEmployees INNER JOIN TblEmployeeHRData ON TblEmployees.EmpEmployeeID = TblEmployeeHRData.HREmployeeID) " & _
    "INNER JOIN Representative ON TblEmployees.EmpFRBEmployeeNumber = Representative.frb_employee_number " & _
    "WHERE Representative.ID = " & RepID & " AND Year([HRDateHired])= " & EvalYear & " AND " & _
    "Month([HRDateHired]) In " & strIn & " "

    Set rst = CurrentDb.OpenRecordset(strSelect, dbOpenSnapshot)
    If rst.RecordCount > 0 Then
        fntCheckEligibilty = 0
    Else
        fntCheckEligibilty = dScore
    End If
    
    
strIneligible = "SELECT DISTINCT Representative.ID AS RepID, TblEmployees.EmpPermanent " & _
    "FROM Representative INNER JOIN TblEmployees ON " & _
    "Representative.frb_employee_number = TblEmployees.EmpFRBEmployeeNumber " & _
    "WHERE TblEmployees.EmpPermanent = 'Permanent' AND TblEmployees.EmpTerminated = No " & _
    "AND TblEmployees.EmpEligible = No "
   
    Set rst = CurrentDb.OpenRecordset(strIneligible, dbOpenSnapshot)
    If rst.RecordCount > 0 Then
        fntCheckEligibilty = 0
    Else
        fntCheckEligibilty = dScore
    End If
    
    rst.Close
    Set rst = Nothing

Exit_ErrorHandler:
    Exit Function
ErrorHandler:
     MsgBox "Error Number: " & Err.Number & vbCrLf & _
           "Description: " & Err.Description & vbCrLf & _
           "Function: fntCheckEligibilty "
    Resume Exit_ErrorHandler
End Function

Open in new window

Comment
Watch Question

Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
Attach a simplified database with few fields and few records that reproduce the issue. List the expected output from the given records.
CERTIFIED EXPERT
Top Expert 2016

Commented:
can you explain
<to loop through the first condtion and if false, then loop through teh next condition and if that is false, return back the value of the dScore.>

what is the first condition,next condition etc..
All you have to do is move the if set both the strings as you have done, then call the first function.
If the result is zero then call the next function and the return the result.

In other words, do all the processing at the end.
See the code below:

Public Function fntCheckEligibilty(ByVal RepID As Integer, ByVal EvalYear As Integer, _
                                 ByVal EvalQuarter As Integer, ByVal dScore As Double) As Double
    'Checks the hire date and if it is in the quarter, sets the IncGoalResult to 0
    'as by business rules, the employee is not eligible.  Also checks the status of the employee to see
    'if they have been manually marked as ineligible by Weston
    On Error GoTo ErrorHandler
    Dim strSelect As String
    Dim strIn As String
    Dim strIneligible As String
    Dim rst As Recordset

    Select Case EvalQuarter
        Case 1
            strIn = "(1,2,3)"
        Case 2
            strIn = "(4,5,6)"
        Case 3
            strIn = "(7,8,9)"
        Case 4
            strIn = "(10,11,12)"
    End Select

    strSelect = "SELECT Representative.ID AS RepID, TblEmployees.EmpHREmployeeNumber, Year([HRDateHired]) AS HireYear, " & _
        "Month([HRDateHired]) AS HireMonth " & _
        "FROM (TblEmployees INNER JOIN TblEmployeeHRData ON TblEmployees.EmpEmployeeID = TblEmployeeHRData.HREmployeeID) " & _
        "INNER JOIN Representative ON TblEmployees.EmpFRBEmployeeNumber = Representative.frb_employee_number " & _
        "WHERE Representative.ID = " & RepID & " AND Year([HRDateHired])= " & EvalYear & " AND " & _
        "Month([HRDateHired]) In " & strIn & " "


    strIneligible = "SELECT DISTINCT Representative.ID AS RepID, TblEmployees.EmpPermanent " & _
        "FROM Representative INNER JOIN TblEmployees ON " & _
        "Representative.frb_employee_number = TblEmployees.EmpFRBEmployeeNumber " & _
        "WHERE TblEmployees.EmpPermanent = 'Permanent' AND TblEmployees.EmpTerminated = No " & _
        "AND TblEmployees.EmpEligible = No "

    rst = CurrentDb.OpenRecordset(strSelect, dbOpenSnapshot)

    If rst.RecordCount > 0 Then
        fntCheckEligibilty = 0
    Else
        rst = CurrentDb.OpenRecordset(strIneligible, dbOpenSnapshot)

        If rst.RecordCount > 0 Then
            fntCheckEligibilty = 0
        Else
            fntCheckEligibilty = dScore
        End If
    End If

    rst.Close()
    rst = Nothing

Exit_ErrorHandler:
    Exit Function
ErrorHandler:
    MsgBox("Error Number: " & Err.Number & vbCrLf & _
          "Description: " & Err.Description & vbCrLf & _
          "Function: fntCheckEligibilty ")
    Resume Exit_ErrorHandler
End Function Class

Open in new window

Sandra SmithRetired

Author

Commented:
Thank you, both conditions as specified in the indicated Select statements were addressed and the problem is solved with your solution.

Sandra

Explore More ContentExplore courses, solutions, and other research materials related to this topic.