How to modify: Filter from a pop-up form

[Heidi]

I'm using the VB below provided in the Knowledge Base article 208529
to filter a report using a pop-up form. I'm not a strong VB user, so
I'm not sure how to modify this code to use different numbers of
filters. Can someone please point me to the right lines that need to
be changed if I want to use two, three, six, etc. filters instead of
the five given in the example?

Thank you for your assistance!


Private Sub Set_Filter_Click()
Dim strSQL As String, IntCounter As Integer

'Build SQL String
For IntCounter = 1 To 5
If Me("Filter" & IntCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & IntCounter).Tag & "] "
& " = " & Chr(34) & Me("Filter" & IntCounter) & Chr(34) & " And "
End If
Next
If strSQL <> "" Then
'Strip Last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))

' Set the Filter property.
Reports![rptCustomers].Filter = strSQL
Reports![rptCustomers].FilterOn = True
End If
End Sub

 

[sanfu at techie dot com]

I'm using the VB below provided in the Knowledge Base article 208529
to filter a report using a pop-up form. I'm not a strong VB user, so
I'm not sure how to modify this code to use different numbers of
filters. Can someone please point me to the right lines that need to
be changed if I want to use two, three, six, etc. filters instead of
the five given in the example?

Thank you for your assistance!


Private Sub Set_Filter_Click()
Dim strSQL As String, IntCounter As Integer

'Build SQL String
For IntCounter = 1 To 5
If Me("Filter" & IntCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & IntCounter).Tag & "] "
& " = " & Chr(34) & Me("Filter" & IntCounter) & Chr(34) & " And "
End If
Next
If strSQL <> "" Then
'Strip Last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))

' Set the Filter property.
Reports![rptCustomers].Filter = strSQL
Reports![rptCustomers].FilterOn = True
End If
End Sub

Heidi,

In the example code, on a form there are 5 controls named Filter1,
Filter2, Filter3, Filter4, and Filter5. The Tag property of each of the
controls holds the name of the field to be filtered on. So if Filter1 is
the control to select the State, then the tag property for Filter1 would
hold "State" (the name of the field in the table).

The For loop goes thru each of the controls and checks to see if there
is anything entered in the control (<> ""). If it is not empty, the
field name in the tag property is added to strSQL, then the value in the
control is added.

Note: that the above example is for string controls which have to be
enclosed in quotes, dates must be enclosed with #'s (ie: #4/1/02#), and
numbers are not enclosed. A Select Case or If..Then statements would be
needed to build a proper strSQL string.


To change the number of filters, just change the 5 in

For IntCounter = 1 To 5


to the number of filters you have:


for 3 filters use >> For IntCounter = 1 To 3

for 6 filters use >> For IntCounter = 1 To 6.



HTH