Results 1 to 7 of 7
  1. #1
    gg80 is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Posts
    328

    Filter after pre Filter problem

    I am using A2007 and A2003. My question involves filtering with VBA . After filtering, I want to button click to add one more filter. I use this:

    Me.Filter = Me.Filter & " AND SERIES= """ & Me.AddOnBox & """"

    It works if the first filter is for only one possible value. If the first filter includes more than one possible value (Me.Filter = "LastName = """ & Me.Filt1Box & """" & " OR LastName = """ & Me.filt2Box & """"), nothing filters.

    How do I make the AND functional with a multiple choice pre filter?. Any help much appreciated.



  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    When you have a mix of And & Or, you should clarify the logic with parentheses. These mean different things:

    (1 Or 2 Or 3) And 4
    1 Or 2 Or (3 And 4)

    You don't want to rely on Access to correctly interpret what you want.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    gg80 is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Posts
    328
    Quote Originally Posted by pbaldy View Post
    When you have a mix of And & Or, you should clarify the logic with parentheses. These mean different things:

    (1 Or 2 Or 3) And 4
    1 Or 2 Or (3 And 4)

    You don't want to rely on Access to correctly interpret what you want.
    I understand-But it doesn’t seem to work in this particular case. This is specifically what I am doing:
    The first filter is (on Change)
    Me.F_SoldSails_Sub.Form.FilterOn = True
    Me.F_SoldSails_Sub.Form.Filter = "[lname] Like ""*" & SrchAll.Text & "*"" or [fname] Like ""*" & SrchAll.Text & "*""" _
    & "or [boat_type] Like ""*" & SrchAll.Text & "*"" or [boatname] Like ""*" & SrchAll.Text & "*"""

    Then after above filtered, the user can click button labeled “J”

    With Me.F_SoldSails_Sub.Form
    Dim Filt As String
    Filt = (.Filter) & " AND SailTypeAbbr like 'J'"
    .Filter = Filt
    End With
    End Sub

    I tried putting parenthesis around first filter and then around reference to first filter as shown above, but still doesn’t work. Is there something special I have to do for a subform?

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Use this on the Filt variable. I don't think it's coming out like you expect. Post the result here if you're still stuck.

    BaldyWeb-Immediate window
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    gg80 is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Posts
    328
    Thanks for follow through, but I'm afraid that the site is beyond my level. I looked it over and can't see even where I would enter my filt expression.

    I find the whole VBA filtering world confusing. The arrangement of " and & and OR/AND, and () seems to have little relation to logic. When I do filtering, I just find examples, then copy them. Even then I find I often have to keep trying this and that until it finally gives up and lets me do what I want. Is there some site somewhere that explains logic of filter expressions?

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It would go here:

    With Me.F_SoldSails_Sub.Form
    Dim Filt As String
    Filt = (.Filter) & " AND SailTypeAbbr like 'J'"
    Debug.Print Filt
    .Filter = Filt
    End With
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    gg80 is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Posts
    328
    I went back and followed through a little more on your first recommendation. Apparently, when I added parentheses, I didn't do it correctly (per my confusion described above). When I modify the original filter expression utilizing the parentheses properly, it works.

    Me.F_SoldSails_Sub.Form.Filter = "([lname] Like ""*" & SrchAll.Text & "*"" or [fname] Like ""*" & SrchAll.Text & "*""" _
    & "or [boat_type] Like ""*" & SrchAll.Text & "*"" or [boatname] Like ""*" & SrchAll.Text & "*"") "

    I didn't realize the referring to previous filter doesn't turn it into an entity, that you still have to be aware of parentheses, etc.. Thanks again, I don't know what I would do without you folks on this site. I will mark as solved, but will still play with the debug site.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 08-16-2012, 01:51 PM
  2. Replies: 28
    Last Post: 03-08-2012, 06:47 PM
  3. filter problem
    By masoud_sedighy in forum Forms
    Replies: 2
    Last Post: 12-15-2011, 01:03 AM
  4. Filter problem
    By Callahan in forum Forms
    Replies: 0
    Last Post: 07-06-2011, 08:03 AM
  5. Filter problem
    By Javad in forum Access
    Replies: 0
    Last Post: 01-22-2011, 01:48 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums