Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filter lesson (Access 2000)

    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) & " <img src=/S/frown.gif border=0 alt=frown width=15 height=15> 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 <img src=/S/confused.gif border=0 alt=confused width=15 height=20> I got a message when I tried to set the filter that I was using a property improperly.
    Reports![rptCustomers].FilterOn = True
    End If

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filter lesson (Access 2000)

    Here is the code copied and pasted from the MSKB article:
    <pre> strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
    & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " _
    And "
    </pre>

    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.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter lesson (Access 2000)

    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?

    <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  4. #4
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter lesson (Access 2000)

    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.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filter lesson (Access 2000)

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

  6. #6
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter lesson (Access 2000)

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

  7. #7
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter lesson (Access 2000)

    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 " ??

  8. #8
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter lesson (Access 2000)

    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 "

  9. #9
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter lesson (Access 2000)

    Did try that. Thats when it gave me the "expected end of statement error." <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filter lesson (Access 2000)

    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.

  11. #11
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter lesson (Access 2000)

    Well I got there! <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •