CODEWITHSUNDEEP
ms-accessvbaif-statement
Javi
Javi

Reputation: 197

MS Access 2010 VBA Multiple Else If Issues

I have an MS Access form where the user can select up to four criteria to filter the records returned by a report. In the VBA, I'm trying to build my filter string dynamically based on which fields, if any, the user chose to filter by. The below code is giving me a "Block If without End If" error. What am I doing wrong here?

If (IsNull(frm!employee) = False) Then
    strFilter = "Trainee_First_Name + ' ' +  Trainee_Last_Name = '" & frm!employee & "'"
    If IsNull(frm!sop) = False Then
        strFilter = strFilter + " AND sop_number = '" & frm!sop & "'"""
    End If
    If IsNull(frm!revision) = False Then
        strFilter = strFilter + " AND revision_number = '" & frm!revision & "'"
    End If
    If IsNull(frm!dept) = False Then
        strFilter = strFilter + " AND department = '" & frm!dept & "'"
    End If
Else
If (IsNull(frm!sop) = False) Then
    strFilter = "sop_number = '" & frm!sop & "'"
    If (IsNull(frm!employee) = False) Then
        strFilter = "Trainee_First_Name + ' ' +  Trainee_Last_Name = '" & frm!employee & "'"
    End If
    If IsNull(frm!revision) = False Then
        strFilter = strFilter + " AND revision_number = '" & frm!revision & "'"
    End If
    If IsNull(frm!dept) = False Then
        strFilter = strFilter + " AND department = '" & frm!dept & "'"
    End If
Else
If (IsNull(frm!revision) = False) Then
    strFilter = "revision_number = '" & frm!revision & "'"
    If (IsNull(frm!employee) = False) Then
        strFilter = " AND Trainee_First_Name + ' ' +  Trainee_Last_Name = '" & frm!employee & "'"
    End If
    If IsNull(frm!sop) = False Then
        strFilter = strFilter + " AND sop_number = '" & frm!sop & "'"""
    End If
    If IsNull(frm!dept) = False Then
        strFilter = strFilter + " AND department = '" & frm!dept & "'"
    End If
Else
If IsNull(frm!dept) = False Then
    strFilter = "department = '" & frm!dept & "'"
    If (IsNull(frm!employee) = False) Then
        strFilter = " AND Trainee_First_Name + ' ' +  Trainee_Last_Name = '" & frm!employee & "'"
    End If
    If IsNull(frm!sop) = False Then
        strFilter = strFilter + " AND sop_number = '" & frm!sop & "'"""
    End If
    If IsNull(frm!revision) = False Then
        strFilter = strFilter + " AND revision_number = '" & frm!revision & "'"
    End If
End If

Any advice you could give me to improve this code would be appreciated.

Upvotes: 2

Views: 8811

Answers (2)

HansUp
HansUp

Reputation: 97131

That code avoids " AND " at the beginning of strFilter. However the logic is challenging to follow and it uses multiple variations on the same basic themes.

Use a simpler approach. You're interested in the values contained in 4 form controls:

  • frm!employee
  • frm!sop
  • frm!Revision
  • frm!dept

Examine each of them in turn and, for any which contain a value, add a segment starting with " AND " to strFilter. Afterwards, if strFilter contains any text, you know it starts with " AND " so you can simply discard the first 5 characters.

strFilter = vbNullString ' <- make it explicit
If Len(Trim(frm!employee) & vbNullString) > 0 Then
    strFilter = strFilter & _
        " AND Trainee_First_Name & ' ' &  Trainee_Last_Name = '" & _
        frm!employee & "'"
End If
If Len(Trim(frm!sop) & vbNullString) > 0 Then
    strFilter = strFilter & " AND sop_number = '" & frm!sop & "'"
End If
If Len(Trim(frm!Revision) & vbNullString) > 0 Then
    strFilter = strFilter & " AND revision_number = '" & _
        frm!Revision & "'"
End If
If Len(Trim(frm!dept) & vbNullString) > 0 Then
    strFilter = strFilter & " AND department = '" & frm!dept & "'"
End If
If Len(strFilter) > 0 Then
    ' discard " AND " from beginning of string
    strFilter = Mid(strFilter, 6)
End If
MsgBox "strFilter ->" & strFilter & "<-"

Upvotes: 4

Tim Lentine
Tim Lentine

Reputation: 7862

The issue is that your else... if clause aren't terminated with end if. You either need to use ElseIf or 

Else
  If ...
  End If
Else...

I've modified your code below to use the latter syntax. Notice the End If lines below.

Note: You don't need **, that's just to call attention to it in your code block!

If (IsNull(frm!employee) = False) Then
        strFilter = "Trainee_First_Name + ' ' +  Trainee_Last_Name = '" & frm!employee & "'"
        If IsNull(frm!sop) = False Then
            strFilter = strFilter + " AND sop_number = '" & frm!sop & "'"""
        End If
        If IsNull(frm!revision) = False Then
            strFilter = strFilter + " AND revision_number = '" & frm!revision & "'"
        End If
        If IsNull(frm!dept) = False Then
            strFilter = strFilter + " AND department = '" & frm!dept & "'"
        End If
    Else
    If (IsNull(frm!sop) = False) Then
        strFilter = "sop_number = '" & frm!sop & "'"
        If (IsNull(frm!employee) = False) Then
            strFilter = "Trainee_First_Name + ' ' +  Trainee_Last_Name = '" & frm!employee & "'"
        End If
        If IsNull(frm!revision) = False Then
            strFilter = strFilter + " AND revision_number = '" & frm!revision & "'"
        End If
        If IsNull(frm!dept) = False Then
            strFilter = strFilter + " AND department = '" & frm!dept & "'"
        End If


**End If**
    Else
    If (IsNull(frm!revision) = False) Then
        strFilter = "revision_number = '" & frm!revision & "'"
        If (IsNull(frm!employee) = False) Then
            strFilter = " AND Trainee_First_Name + ' ' +  Trainee_Last_Name = '" & frm!employee & "'"
        End If
        If IsNull(frm!sop) = False Then
            strFilter = strFilter + " AND sop_number = '" & frm!sop & "'"""
        End If
        If IsNull(frm!dept) = False Then
            strFilter = strFilter + " AND department = '" & frm!dept & "'"
        End If
    **End If**
    Else
    If IsNull(frm!dept) = False Then
        strFilter = "department = '" & frm!dept & "'"
        If (IsNull(frm!employee) = False) Then
            strFilter = " AND Trainee_First_Name + ' ' +  Trainee_Last_Name = '" & frm!employee & "'"
        End If
        If IsNull(frm!sop) = False Then
            strFilter = strFilter + " AND sop_number = '" & frm!sop & "'"""
        End If
        If IsNull(frm!revision) = False Then
            strFilter = strFilter + " AND revision_number = '" & frm!revision & "'"
        End If
    **End If**
    End If

Upvotes: 2

Related Questions