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
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE