Multiple Filter Criteria

[PJFry]

I am developing a form where the user can select a check box to filter a
form.

I can sucessfully filter using one criteria:

This sets the filter value:

Private Sub chkPending_BeforeUpdate(Cancel As Integer)

Select Case Me.chkPending
Case -1
chkPending1 = "Pending"
Case 0
chkPending1 = ""
End Select

End Sub

And this filters:
Private Sub chkPending_AfterUpdate()
Me.Filter = "txtStatus = '" & chkPending1 & "'"

Me.FilterOn = True
End Sub

There will be eight check boxes on this form. How do I string those values
together to filter. In english, it would read:
Filter txtStatus on Pending, Open and closed.

Thoughts?

PJ

 

[Ken Snell MVP]

Build a string that looks like this:

Me.Filter = "txtStatus = '" & chkPending1 & "' And txtNextItem = '" &
chkNextName & "' And txtAnotherItem = '" & chkAnotherName & "'"

You can string together as many as you want, so long as the above syntax is
met.

 

[tonynahon]

Build a string that looks like this:

Me.Filter = "txtStatus = '" & chkPending1 & "' And txtNextItem = '"&
chkNextName & "' And txtAnotherItem = '" & chkAnotherName & "'"

You can string together as many as you want, so long as the above syntax is
met.

PJ: I had a similar situation where I needed to filter a form on
multiple criteria. In your case I have assumed that you have a field
(txtStatus) which can contain the values 'Open', 'Pending', 'Closed'
and 5 other possibilities and you want to filter on these values,
depending on the selection on the form. I'm not sure that you can or
want to set the filter after the chkPending Update, or any of the
individual check boxes - I think you need a separate command button to
trigger the filter when the user has completed the selections. But
after you have primed chkPending1, chkOpen1, chkCLosed1, and all the
other check box values, in the Click event for the command button, you
would have code something like this:

Dim strFilter As String
strFilter = ""

If chkPending1 Then
strFilter = "txtStatus = '" & chkPending1 & "'"
end if

If chkOpen1 Then
If Len(strFilter) > 0 the strFilter = strFilter & " AND "
strFilter = strFilter & "txtStatus = '" & chkOpen1 & "'"
End If

If chkClosed1 Then
If Len(strFilter) > 0 the strFilter = strFilter & " AND "
strFilter = strFilter & "txtStatus = '" & chkClosed1 & "'"
End If

Me.Filter = strFilter
Me.FilterOn = True

I hope this helps. . .

Tony N.

 

[tonynahon]

PJ: I had a similar situation where I needed to filter a form on
multiple criteria. In your case I have assumed that you have a field
(txtStatus) which can contain the values 'Open', 'Pending', 'Closed'
and 5 other possibilities and you want to filter on these values,
depending on the selection on the form. I'm not sure that you can or
want to set the filter after the chkPending Update, or any of the
individual check boxes - I think you need a separate command button to
trigger the filter when the user has completed the selections. But
after you have primed chkPending1, chkOpen1, chkCLosed1, and all the
other check box values, in the Click event for the command button, you
would have code something like this:

Dim strFilter As String
strFilter = ""

If chkPending1 Then
   strFilter = "txtStatus = '" & chkPending1 & "'"
end if

If chkOpen1 Then
   If Len(strFilter) > 0 the strFilter = strFilter & " AND "
   strFilter = strFilter & "txtStatus = '" & chkOpen1 & "'"
End If

If chkClosed1 Then
   If Len(strFilter) > 0 the strFilter = strFilter & " AND "
   strFilter = strFilter & "txtStatus = '" & chkClosed1 & "'"
End If

Me.Filter = strFilter
Me.FilterOn = True

I hope this helps. . .

Tony N.

Maybe I should proofread before I post! Those should be ORs not ANDs.

 

[PJFry]

Tony,

That did the trick. Here is what it ended up looking like:
Private Sub btnApplyFilter_Click()
Dim strFilter As String
strFilter = ""

If Me.chkPending = -1 Then
strFilter = "txtStatus = 'Pending'"
Else
strFilter = ""
End If

If Me.chkOpen = -1 Then
If Len(strFilter) > 0 Then
strFilter = strFilter & " OR "
strFilter = strFilter & "txtStatus = 'Open'"
ElseIf Len(strFilter) = 0 Then
strFilter = "txtStatus = 'Open'"
End If
ElseIf Me.chkOpen = 0 Then
strFilter = strFilter
End If

If Me.chkClosed = -1 Then
If Len(strFilter) > 0 Then
strFilter = strFilter & " OR "
strFilter = strFilter & "txtStatus = 'Closed'"
ElseIf Len(strFilter) = 0 Then
strFilter = "txtStatus = 'Closed'"
End If
ElseIf Me.chkClosed = 0 Then
strFilter = strFilter
End If

If Me.chkOnHold = -1 Then
If Len(strFilter) > 0 Then
strFilter = strFilter & " OR "
strFilter = strFilter & "txtStatus = 'On Hold'"
ElseIf Len(strFilter) = 0 Then
strFilter = "txtStatus = 'On Hold'"
End If
ElseIf Me.chkOnHold = 0 Then
strFilter = strFilter
End If

Me.Filter = strFilter
Me.FilterOn = True

End Sub

I made the If statements more explicit so I could make sure I could follow
the flow.

Thanks!

PJ