Public Function GetRepPersonalInfoEOM(ByVal RepID As Integer, ByVal EvalYear As Integer, ByVal EvalQuarter As Integer, ByVal Description As String) As String
On Error GoTo ErrorHandler
'Employee of the month is handled a little differently, first need to check to see if managers have inputted a
'value, it not, then do not bring in data as it may overwrite what has been entered from the Accountabilities side
Dim strSelect As String
Dim strEOM As String
Dim strDesc As String
Dim strCol As String
Dim rst As Recordset
Dim rs As Recordset
If Description = "Employee of the Month ($$$)" Then
strEOM = "SELECT Representative.ID, TblIncentives.IncYear, TblIncentives.IncQuarter, TblIncentives.IncEOM " & _
"FROM (TblEmployees INNER JOIN TblIncentives ON TblEmployees.EmpEmployeeID = TblIncentives.IncEmployeeID) " & _
"INNER JOIN Representative ON TblEmployees.EmpFRBEmployeeNumber = Representative.frb_employee_number " & _
"WHERE Representative.ID = " & RepID & " AND TblIncentives.IncYear = " & EvalYear & " AND " & _
"TblIncentives.IncQuarter = " & EvalQuarter & " AND TblIncentives.IncEOM > 0 "
Set rst = CurrentDb.OpenRecordset(strEOM, dbOpenSnapshot)
If rst.RecordCount > 0 Then
GetRepPersonalInfoEOM = (rst!IncEOM)
Exit Function
Else
strSelect = "SELECT value " & _
"FROM EmployeeInfo " & _
"WHERE ID=" & RepID & " AND eval_quarter=" & EvalQuarter & " AND eval_year=" & EvalYear & " " & _
"AND description='" & Description & "' AND Nz(value, 0) > 0 "
Set rs = CurrentDb.OpenRecordset(strSelect, dbOpenSnapshot)
If rs.RecordCount > 0 Then
GetRepPersonalInfoEOM = Nz(rs!value)
End If
End If
ElseIf Description = "Employee of the Month Nominee ($$)" Then
strEOM = "SELECT Representative.ID, TblIncentives.IncYear, TblIncentives.IncQuarter, TblIncentives.IncOther " & _
"FROM (TblEmployees INNER JOIN TblIncentives ON TblEmployees.EmpEmployeeID = TblIncentives.IncEmployeeID) " & _
"INNER JOIN Representative ON TblEmployees.EmpFRBEmployeeNumber = Representative.frb_employee_number " & _
"WHERE Representative.ID = " & RepID & " AND TblIncentives.IncYear = " & EvalYear & " AND " & _
"TblIncentives.IncQuarter = " & EvalQuarter & " AND TblIncentives.IncOther > 0 "
Set rst = CurrentDb.OpenRecordset(strEOM, dbOpenSnapshot)
If rst.RecordCount > 0 Then
GetRepPersonalInfoEOM = (rst!IncOther)
Exit Function
Else
strSelect = "SELECT value " & _
"FROM EmployeeInfo " & _
"WHERE ID=" & RepID & " AND eval_quarter=" & EvalQuarter & " AND eval_year=" & EvalYear & " " & _
"AND description='" & Description & "' AND Nz(value, 0) > 0 "
Set rs = CurrentDb.OpenRecordset(strSelect, dbOpenSnapshot)
If rs.RecordCount > 0 Then
GetRepPersonalInfoEOM = Nz(rs!value)
End If
End If
End If
Exit_ErrorHandler:
Exit Function
ErrorHandler:
MsgBox "Error Number: " & Err.Number & vbCrLf & _
"Description: " & Err.Description & vbCrLf & _
"Function: GetRepPersonalInfoEOM "
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