Need help with this filter VBA (1 Viewer)

[gojets1721]

I'm having trouble coding this filter to work correctly with two filters. It's a search box in a form allowing the user to input a department that they want to filter to.

I want it to filter down to department AND THEN filter those results to the year listed in the combo box. The way I have it coded below does so in a way that the second filter just cancels out the first. Any suggestions on how to recode this?

    Dim S As String
    
    S = InputBox("Enter Department", "Department Search")
    If S = "" Then Exit Sub
    
    Me.Filter = "Department LIKE ""*" & S & "*"""
    Me.Filter = "YEAR([ComplaintDate])= " & Me.ComboYearSelection
    DoCmd.SetOrderBy "[ComplaintDate] ASC"
    Me.FilterOn = True

 

[plog]

1. You are telling your filter to equal 1 thing, then you immediately tell it to equal another. You are not adding to that first thing, you are just resetting it. You want your filter to keep what it has and add some more stuff to it. That would be done generally like this:

Filter = "ABC"
Filter = Filter & "XYZ"

You keep whats in it and add to it.

2. Logically, you need to make sure that add the parts inside the string together correctly. Just like you use & to merge variables and strings outside of quote marks, inside quoate marks you need to use AND to combine logical tests:

Filter = "(x=1) AND (y=2)"

Give both those a shot on your data and post back here what you have tried. (Or wait 10 minutes and someone will spoonfeed you the answer)

 

[gojets1721]

1. You are telling your filter to equal 1 thing, then you immediately tell it to equal another. You are not adding to that first thing, you are just resetting it. You want your filter to keep what it has and add some more stuff to it. That would be done generally like this:

Filter = "ABC"
Filter = Filter & "XYZ"

You keep whats in it and add to it.

2. Logically, you need to make sure that add the parts inside the string together correctly. Just like you use & to merge variables and strings outside of quote marks, inside quoate marks you need to use AND to combine logical tests:

Filter = "(x=1) AND (y=2)"

Give both those a shot on your data and post back here what you have tried. (Or wait 10 minutes and someone will spoonfeed you the answer)

Your number 2 option worked!! Thanks!