We help IT Professionals succeed at work.

Else in If statement without having to repeat code

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

CERTIFIED EXPERT
Top Expert 2016

Commented:

place the strSelect statement before the If then statement..

or use

select Case <variable>

      case <value1>
         'code here


      case <value2>
         'code here

end select


Sandra SmithRetired

Author

Commented:
Ok, so I make it the first statement in the if?  Let me try that and let you know how it works.
Sandra SmithRetired

Author

Commented:
OK, that won't work work.  I have to process two conditions before I get to the select statement.  The first two descriptions are checks adn for each, a record count is done, only then does the select staetment get processed if the recordcound is 0 for the description.  I cannot process the select first as it is really only if there is no recordcound for the description.  What is happening, if there is a recorcount for the repid, then I need a value from TblIncentives for the return value, if there is no recordcount, then I need to return the value from the EmployeeInfo table back to the calling procedure.
Retired
Commented:
Actually, I solved it by using some variables for the select statement columns. It is much neater, but thank you for your help
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 strCol  As String
Dim rst As Recordset
Dim rs As Recordset

If Description = "Employee of the Month ($$$)" Then
    strCol = "IncEOM"
ElseIf Description = "Employee of the Month Nominee ($$)" Then
    strCol = "IncOther"
End If

strEOM = "SELECT TblIncentives." & strCol & " As EOM 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." & strCol & " > 0 "
        
        Set rst = CurrentDb.OpenRecordset(strEOM, dbOpenSnapshot)
        If rst.RecordCount > 0 Then
            GetRepPersonalInfoEOM = (rst!EOM)
            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)
            Else
                GetRepPersonalInfoEOM = ""
            End If
        End If
Set rst = Nothing
Set rs = Nothing

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

Sandra SmithRetired

Author

Commented:
Solved it myself. Reduced it to soem variables rather than having two separate if statements with basically the same Select statement while only one of the coumns was different.
CERTIFIED EXPERT
Top Expert 2016

Commented:
;-(

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