Filter lesson (Access 2000)

Topic

New Reply

In my ongoing efforts to actually learn this stuff, I’m trying to work through “How to Filter a Report from a Pop-Up Form as presented in Microsoft Knowledge Base Article – 208529.

I keep getting a compile error from the set-filter command button.

Here is what I have typed

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) & ” This is the line that I’m getting a message on . Compile error: Expected line number or label or statement or end of statement. There is also an And ” on the next line that it doesn’t like either. I’ve tried everything I can think of. If I have typed it differently than the instructions, I can’t see it. Please point out what simple thing I’m overlooking.

And ”
End If
Next
If strSQL ” ” Then
‘ Strip Last ” And ”
strSQL = Left(strSQL, (Len(strSQL) – 5))

‘ Set the Filter Property
Reports![rptCustomers].Filter = strSQL I got a message when I tried to set the filter that I was using a property improperly.
Reports![rptCustomers].FilterOn = True
End If

Reply | Quote

Replies

Here is the code copied and pasted from the MSKB article:

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

Notice that the first and second line end in a space and an underscore. The underscore is called the line continuation character; it signifies that the instruction is not complete yet, and will be continued on the next line. You haven’t included the space+underscore in your code, so Visual Basic tries to interpret the above code as three separate instructions, but they aren’t valid as such.

Reply | Quote

Thank you Hans. One problem solved. Now — I’m getting a type mismatch error message. The field referenced in the filters are all text fields. Now what?

Reply | Quote

Lady,

A couple things to check: (1) Make sure that final ” And ” is just 5 characters (both a leading and following space, all within the quotes). That’s what’s stripped off after the For Loop. (2) If you’re still getting an error, try setting a breakpoint on the Reports![rptCustomers].Filter = strSQL statement. When execution stops there, type ?strSQL in the immediate window and see what the string looks like and if there’s anything that looks peculiar.

Hope this helps.

Reply | Quote

Yeeep!!! When I changed the And, it gave me a message that it expected an end of statment. I put it back, and at least I get no message. tihs is how it looks

strSQL = strSQL & “[” & Me(“Filter” & intCounter).Tag & “] ” _
& ” = ” & Chr(34) & Me(“Filter” & intCounter) & Chr(34) & “” _
And “”

could the 5 spaces be And ” ??

Reply | Quote

Something looks a little odd with the end of your statement (the last continuation and the pairs of quotes near the “and” conjunction). Try this:

strSQL = strSQL & “[” & Me(“Filter” & intCounter).Tag & “] ” _
& ” = ” & Chr(34) & Me(“Filter” & intCounter) & Chr(34) & _
” And “

Reply | Quote

Did try that. Thats when it gave me the “expected end of statement error.”

Reply | Quote

From the bits of code you post, it seems you are not copying the code from the MSKB article exactly. Be very careful, any quote in the wrong place will cause an error.

I recommend downloading the sample database mentioned in the MSKB article. The direct link is ACC2000: Microsoft Access 2000 Sample Reports Available in Download Center. This database contains a working example.

Reply | Quote

Well I got there! I finally just copied the text from rptSamp00 directly into my module. I still don’t see a difference, but it must be there. That solved the coding error, but my form kept asking for parameters. TAG (of course you know this.)
I have to say that I’m not that impressed with Microsofts lessons, but you guys ROCK!!
Thanks so much for your help. Now to try to put one of these forms in my actual database. Wish me luck.

Reply | Quote

Well I got there! I finally just copied the text from rptSamp00 directly into my module. I still don’t see a difference, but it must be there. That solved the coding error, but my form kept asking for parameters. TAG (of course you know this.)
I have to say that I’m not that impressed with Microsofts lessons, but you guys ROCK!!
Thanks so much for your help. Now to try to put one of these forms in my actual database. Wish me luck.

Reply | Quote

From the bits of code you post, it seems you are not copying the code from the MSKB article exactly. Be very careful, any quote in the wrong place will cause an error.

I recommend downloading the sample database mentioned in the MSKB article. The direct link is ACC2000: Microsoft Access 2000 Sample Reports Available in Download Center. This database contains a working example.

Reply | Quote

Did try that. Thats when it gave me the “expected end of statement error.”

Reply | Quote

Something looks a little odd with the end of your statement (the last continuation and the pairs of quotes near the “and” conjunction). Try this:

strSQL = strSQL & “[” & Me(“Filter” & intCounter).Tag & “] ” _
& ” = ” & Chr(34) & Me(“Filter” & intCounter) & Chr(34) & _
” And “

Reply | Quote

Yeeep!!! When I changed the And, it gave me a message that it expected an end of statment. I put it back, and at least I get no message. tihs is how it looks

strSQL = strSQL & “[” & Me(“Filter” & intCounter).Tag & “] ” _
& ” = ” & Chr(34) & Me(“Filter” & intCounter) & Chr(34) & “” _
And “”

could the 5 spaces be And ” ??

Reply | Quote

Lady,

A couple things to check: (1) Make sure that final ” And ” is just 5 characters (both a leading and following space, all within the quotes). That’s what’s stripped off after the For Loop. (2) If you’re still getting an error, try setting a breakpoint on the Reports![rptCustomers].Filter = strSQL statement. When execution stops there, type ?strSQL in the immediate window and see what the string looks like and if there’s anything that looks peculiar.

Hope this helps.

Reply | Quote

On which line of the code do you get the Type Mismatch error?

Reply | Quote

It is highligting the exact code you copied and pasted from the article. All of it.
Thanks for your interest

Reply | Quote

It is highligting the exact code you copied and pasted from the article. All of it.
Thanks for your interest

Reply | Quote

On which line of the code do you get the Type Mismatch error?

Reply | Quote

Thank you Hans. One problem solved. Now — I’m getting a type mismatch error message. The field referenced in the filters are all text fields. Now what?

Reply | Quote

Here is the code copied and pasted from the MSKB article:

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

Notice that the first and second line end in a space and an underscore. The underscore is called the line continuation character; it signifies that the instruction is not complete yet, and will be continued on the next line. You haven’t included the space+underscore in your code, so Visual Basic tries to interpret the above code as three separate instructions, but they aren’t valid as such.

Reply | Quote