We help IT Professionals succeed at work.
Get Started

Else in If statement without having to repeat code

332 Views
Last Modified: 2012-05-12
The code attached works, but it is messy.  What I need is if there is no record count, the code simply runs the strSelect statement.  However, I am trying to eliinate the repeated code for the select statement in each of the if statements, am looking for suggestions on how to make this a little more elegant, right now it is simply blunt force processing.
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

Open in new window

Comment
Watch Question
Retired
Commented:
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
Why Experts Exchange?

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.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE