Filter Function for combo boxes (1 Viewer)

[dkinnz]

Hi ya'll,

I'm having difficulty getting my combo box filtering working the way I'd like it to. The problem that I'm having is the fact that I'm performing "OR" cases across three columns of data looking for values that match what's selected in those two combo boxes. So then when I go to filter my data using the other combo boxes "Location" (cboLocation) or "Status" (cboStatus), no filtering takes place b/c it's still "holding onto" the OR case from the previous combo boxes.
It's easier if you see my attached database, but here's the code I have right now that's causing all the trouble:

Private Sub cmdSearch_Click()
    Me.subfrmFilter1.Form.RecordSource = "SELECT * FROM qryallgroups " & FilterIt
    Me.subfrmFilter1.Requery

End Sub

Private Function FilterIt() As Variant
    Dim varFam As Variant

varFam = "1=1 "
'The first two If statements I think are the cause of my problems...
If Me.cboGlobal <> "(ALL)" Then
    varFam = varFam & "and [Global Line1]='" & Me.cboGlobal & "' "
    varFam = varFam & "or [Global Line2]='" & Me.cboGlobal & "' "
    varFam = varFam & "or [Global Line3]='" & Me.cboGlobal & "' "
End If

If Me.cboGroup <> "(ALL)" Then
    varFam = varFam & "and [Group Name1]='" & Me.cboGroup & "' "
    varFam = varFam & "or [Group Name2]='" & Me.cboGroup & "' "
    varFam = varFam & "or [Group Name3]='" & Me.cboGroup & "' "
End If
If Me.cboLocation <> "(ALL)" Then
    varFam = varFam & "and [Location]='" & Me.cboLocation & "' "
End If

If Me.cboStatus <> "(ALL)" Then
    varFam = varFam & "and [Status]='" & Me.cboStatus & "'"
End If

FilterIt = "WHERE " & varFam

End Function

THANK YOU IF YOU CAN HELP!!!!!!!!!!!!!!!!!!!!!!!

 

[pbaldy]

You want to add some parentheses so instead of

1=1 AND a=1 OR b=1 OR c=1 AND x=2 OR y=2 OR z=2

you get

1=1 AND (a=1 OR b=1 OR c=1) AND (x=2 OR y=2 OR z=2)