Filter a Report from a Pop-Up Form

[Heidi]

I'm using the code provided in Knowledge Base Article 208529 for using
a form to filter a report. My understanding is that, in this example,
you should be able to select an option from one or more of the combo
boxes in order to filter the report. However, when testing the form
and trying to select only from a single combo box I receive the error
"Extra ) in query expression '([Region]= "BC" And)'. I can't find an
extra ) in this code. Can anyone point out what I'm missing? Thank you
in advance.


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

 

[Gerald Stanley]

It looks to me as if you have too many spaces trailing the
'And' when you are building up your SQL with the result
that when you truncate the last 5 characters, the And word
still remains. There should only be 1 space either side e.g.

strSQL = strSQL & "[" & Me("Filter" & IntCounter).Tag & "]
" & " = " & Chr(34) & Me("Filter" & IntCounter) & Chr(34) &
" And "

Hope This Helps
Gerald Stanley MCSD

 

[Heidi]

The KB article definitely showed 5 spaces after the And; but removing
them fixed the problem. Thank you very much!

It looks to me as if you have too many spaces trailing the
'And' when you are building up your SQL with the result
that when you truncate the last 5 characters, the And word
still remains. There should only be 1 space either side e.g.

strSQL = strSQL & "[" & Me("Filter" & IntCounter).Tag & "]
" & " = " & Chr(34) & Me("Filter" & IntCounter) & Chr(34) &
" And "

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
I'm using the code provided in Knowledge Base Article 208529 for using
a form to filter a report. My understanding is that, in this example,
you should be able to select an option from one or more of the combo
boxes in order to filter the report. However, when testing the form
and trying to select only from a single combo box I receive the error
"Extra ) in query expression '([Region]= "BC" And)'. I can't find an
extra ) in this code. Can anyone point out what I'm missing? Thank you
in advance.


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]

A follow-up question: how do I modify this code to work with different
numbers of filters? For example, if I only want to use two combo
boxes, I assume I need to modify the range given to intCounter, but
what else needs to be changed?

Thanks!

It looks to me as if you have too many spaces trailing the
'And' when you are building up your SQL with the result
that when you truncate the last 5 characters, the And word
still remains. There should only be 1 space either side e.g.

strSQL = strSQL & "[" & Me("Filter" & IntCounter).Tag & "]
" & " = " & Chr(34) & Me("Filter" & IntCounter) & Chr(34) &
" And "

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
I'm using the code provided in Knowledge Base Article 208529 for using
a form to filter a report. My understanding is that, in this example,
you should be able to select an option from one or more of the combo
boxes in order to filter the report. However, when testing the form
and trying to select only from a single combo box I receive the error
"Extra ) in query expression '([Region]= "BC" And)'. I can't find an
extra ) in this code. Can anyone point out what I'm missing? Thank you
in advance.


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
.