VBA Multi-"And" & "Or" Filter not applying correctly-CodePudding

I am making a database that stores all the components of a large electromechanical device. The database stores reference information (component type, system, subsystem etc) for all components as well as component details (width, material etc). I am working on a form with combo boxes that the user fills out to produce a report with only the specific components that match the filter criteria Form with selections for component type and subsystem made. I can generate the report fine but it doesn't filter correctly all the time. I believe it has to do with the multi-filter string I'm applying. As an example, if I've selected Component Type = Ball Bearing or Shaft and Subsystem = Mooring 1 or Floats 2. The string that I apply as the filter is:

[ComponentType] = 'Ball Bearing' Or [ComponentType] = 'Shaft' And [Subsystem] = 'Mooring 1' Or [Subsystem] = 'Floats 2'

But when I get the report it has entries that it should not haveFiltered report. This report should only have Shafts or Ball Bearings that are in the Mooring 1 or Floats 2 Subsystem. This example only has 1 item that shouldn't be there but it happens with other filters as well. I select only X and Y Component Types in B Subsystem, yet it returns with X, Y and Z Component Types in B Subsystem.

Here is my code.

Private Sub Report_Open(Cancel As Integer)

Dim frm As Form 'sets frm to a Form type
Dim strFilter, comptypestrFilter, systemstrFilter, subsystemstrFilter, assemblystrFilter, subassemblystrFilter As String 'set strFilter as string type
Set frm = Forms!FilterComponentListFrm 'form must be open. makes frm into abbreviation
strFilter = "" 'Create empty filter string
comptypestrFilter = ""  'Create empty filter string for comptype
systemstrFilter = ""  'Create empty filter string for system
subsystemstrFilter = ""  'Create empty filter string for subsystem
assemblystrFilter = ""  'Create empty filter string for assembly
subassemblystrFilter = ""  'Create empty filter string for subassembly

'Check if length of all combo boxes plus a blank string=0. If True, Don't apply filter and exit sub.
If Len("" & frm![ComponentTypeFilterCombo] & frm![SystemFilterCombo] & frm![SubsystemFilterCombo] & frm![AssemblyFilterCombo] & frm![SubassemblyFilterCombo] & _
frm![ComponentTypeFilterCombo2] & frm![SystemFilterCombo2] & frm![SubsystemFilterCombo2] & frm![AssemblyFilterCombo2] & frm![SubassemblyFilterCombo2]) = 0 Then
    Me.Filter = ""
    Me.FilterOn = False
    Exit Sub

'If length of sum of all combo boxes <>0
Else
    'The first 5 blocks of If/ElseIf/ElseIf/EndIf check to see if the combo boxes are selected or empty and puts the expression into a string.
    'If CompTypeFilterCombo 1 and 2 is selected then set comptype filter string as CompTypeFilter 1 and 2
    If Len("" & frm![ComponentTypeFilterCombo]) > 0 And Len("" & frm![ComponentTypeFilterCombo2]) > 0 Then
        comptypestrFilter = "[ComponentType] = '" & frm![ComponentTypeFilterCombo] & "'" & " Or " & "[ComponentType] = '" & frm![ComponentTypeFilterCombo2] & "'"
    'If only CompTypeFilterCombo 1 is selected then set comptype filter string as CompTypeFilter 1
    ElseIf Len("" & frm![ComponentTypeFilterCombo]) > 0 Then
        comptypestrFilter = "[ComponentType] = '" & frm![ComponentTypeFilterCombo] & "'"
    'If only CompTypeFilterCombo 2 is selected then set comptype filter string as CompTypeFilter 2
    ElseIf Len("" & frm![ComponentTypeFilterCombo2]) > 0 Then
        comptypestrFilter = "[ComponentType] = '" & frm![ComponentTypeFilterCombo2] & "'"
    End If
    
    'If SystemFilterCombo 1 and 2 is selected then set system filter string as systemfiter 1 and 2
    If Len("" & frm![SystemFilterCombo]) > 0 And Len("" & frm![SystemFilterCombo2]) > 0 Then
        systemstrFilter = "[System] = '" & frm![SystemFilterCombo] & "'" & " Or " & "[System] = '" & frm![SystemFilterCombo2] & "'"
    'If SystemFilterCombo 1 is selected only, then add SystemFilter 1 into system filter string
    ElseIf Len("" & frm![SystemFilterCombo]) > 0 Then
        systemstrFilter = "[System] = '" & frm![SystemFilterCombo] & "'"
    'If SystemFilterCombo 2 is selected only, then add SystemFilter 2 into system filter string
    ElseIf Len("" & frm![SystemFilterCombo2]) > 0 Then
        systemstrFilter = "[System] = '" & frm![SystemFilterCombo2] & "'"
    End If
    
    'If SubsystemFilterCombo 1 and 2 is selected then set subsystem filter string as subsystemfiter 1 and 2
    If Len("" & frm![SubsystemFilterCombo]) > 0 And Len("" & frm![SubsystemFilterCombo2]) > 0 Then
        subsystemstrFilter = "[Subsystem] = '" & frm![SubsystemFilterCombo] & "'" & " Or " & "[Subsystem] = '" & frm![SubsystemFilterCombo2] & "'"
    'If SubsystemFilterCombo 1 is selected only, then add SubsystemFilter 1 into subsystem filter string
    ElseIf Len("" & frm![SubsystemFilterCombo]) > 0 Then
        subsystemstrFilter = "[Subsystem] = '" & frm![SubsystemFilterCombo] & "'"
    'If SubsystemFilterCombo 2 is selected only, then add SubsystemFilter 2 into subsystem filter string
    ElseIf Len("" & frm![SubsystemFilterCombo2]) > 0 Then
        subsystemstrFilter = "[Subsystem] = '" & frm![SubsystemFilterCombo2] & "'"
    End If
    
    'If AssemblyFilterCombo 1 and 2 is selected then set Assembly filter string as Assembly 1 and 2
    If Len("" & frm![AssemblyFilterCombo]) > 0 And Len("" & frm![AssemblyFilterCombo2]) > 0 Then
        assemblystrFilter = "[Assembly] = '" & frm![AssemblyFilterCombo] & "'" & " Or " & "[Assembly] = '" & frm![AssemblyFilterCombo2] & "'"
    'If AssemblyFilterCombo 1 is selected only, then add AssemblyFilter 1 into assembly filter string
    ElseIf Len("" & frm![AssemblyFilterCombo]) > 0 Then
        assemblystrFilter = "[Assembly] = '" & frm![AssemblyFilterCombo] & "'"
    'If AssemblyFilterCombo 2 is selected only, then add AssemblyFilter 2 into assembly filter string
    ElseIf Len("" & frm![AssemblyFilterCombo2]) > 0 Then
        assemblystrFilter = "[Assembly] = '" & frm![AssemblyFilterCombo2] & "'"
    End If

    'If SubassemblyFilterCombo 1 and 2 is selected then set Subassembly filter string as Subassembly 1 and 2
    If Len("" & frm![SubassemblyFilterCombo]) > 0 And Len("" & frm![SubassemblyFilterCombo2]) > 0 Then
        subassemblystrFilter = "[Subassembly] = '" & frm![SubassemblyFilterCombo] & "'" & " Or " & "[Subassembly] = '" & frm![SubassemblyFilterCombo2] & "'"
    'If SubassemblyFilterCombo 1 is selected only, then add SubassemblyFilter 1 into subassembly filter string
    ElseIf Len("" & frm![SubassemblyFilterCombo]) > 0 Then
        subassemblystrFilter = "[Subassembly] = '" & frm![SubassemblyFilterCombo] & "'"
    'If SubassemblyFilterCombo 2 is selected only, then add SubassemblyFilter 2 into subassembly filter string
    ElseIf Len("" & frm![SubassemblyFilterCombo2]) > 0 Then
        subassemblystrFilter = "[Subassembly] = '" & frm![SubassemblyFilterCombo2] & "'"
    End If
    
    'Checks if each of the individual filters and the overal strFilter is empty. If individual filter and strFilter is empty, sets individual filter to strFilter. If strFilter is not empty, concatenate individual filter onto current strFilter
    'Compiles strFilter based on which comboboxes are selected
    If Len("" & comptypestrFilter) > 0 Then 'set strFilter to comptypefilter if any comptypefiltercombo box is selected
        strFilter = comptypestrFilter
    End If
    
    If Len("" & systemstrFilter) > 0 And Len("" & strFilter) > 0 Then 'if systemfilter and strFilter are > 0 then concatenate
        strFilter = strFilter & " And " & systemstrFilter
    ElseIf Len("" & systemstrFilter) > 0 Then 'if strFilter is not >0 then set strFilter to system Filter
        strFilter = systemstrFilter
    End If
    
    If Len("" & subsystemstrFilter) > 0 And Len("" & strFilter) > 0 Then 'if subsystemfilter and strFilter are > 0 then concatenate
        strFilter = strFilter & " And " & subsystemstrFilter
    ElseIf Len("" & subsystemstrFilter) > 0 Then 'if strFilter is not >0 then set strFilter to subsystem Filter
        strFilter = subsystemstrFilter
    End If
    
    If Len("" & assemblystrFilter) > 0 And Len("" & strFilter) > 0 Then 'if assemblyfilter and strFilter are > 0 then concatenate
        strFilter = strFilter & " And " & assemblystrFilter
    ElseIf Len("" & assemblystrFilter) > 0 Then 'if strFilter is not >0 then set strFilter to assembly Filter
        strFilter = assemblystrFilter
    End If

    If Len("" & subassemblystrFilter) > 0 And Len("" & strFilter) > 0 Then 'if subassemblyfilter and strFilter are > 0 then concatenate
        strFilter = strFilter & " And " & subassemblystrFilter
    ElseIf Len("" & subassemblystrFilter) > 0 Then 'if strFilter is not >0 then set strFilter to subassembly Filter
        strFilter = subassemblystrFilter
    End If
    
    MsgBox strFilter, 0, "strFilter"
        
    Me.Filter = strFilter
    Me.FilterOn = True
End If


End Sub

CodePudding user response：

You need parentheses around the expressions.

([ComponentType] = 'Ball Bearing' Or [ComponentType] = 'Shaft') And ([Subsystem] = 'Mooring 1' Or [Subsystem] = 'Floats 2')

It's an operator precedence issue. AND is processed before OR, so your previous code was being read as

[ComponentType] = 'Ball Bearing' Or ([ComponentType] = 'Shaft' And ([Subsystem] = 'Mooring 1') Or [Subsystem] = 'Floats 2'.

Adding the parentheses controls the way that it is processed. The two OR expressions are done first, and then the AND is applied to the result, giving you the behavior you want.