Filtering a form by several fields

[sagi871]

Hey,

I'm using 4 combo-boxes in order to filter a tabular form in access.
One combo box is for a company name the second is for a report type and the third and forth is for choosing years.
I used VBA in order to apply the filter:

Me.Filter = "[Company].Value Like ""*"" & '" & Combo108.Value & "' & ""*"""
Me.Filter = "[Report_Type] =" & "'" & Me.Combo123.Value & "'" & ""
Me.Filter = "[Rep_Year] Between " & Combo125.Value & " and " & Combo127.Value

Each line works by itself but I cant figure out how to combine them together, or how to use "AND" between each filter so the filtering will refer all of them when I run the code.

 

[pr2-eugin]

You have to use AND to join them togeather

Me.Filter = "([Company].Value Like '*" & Me.Combo108 & "*') AND ([Report_Type] = '" & Me.Combo123 & "') AND ([Rep_Year] Between " & Me.Combo125 & " AND " & Me.Combo127 & ")"
Me.FilterOn = True

 

[sagi871]

Thanks it works!
but if one of the comboxes is empty then I get syntax error. Is there a way the solve it?

 

[pr2-eugin]

You can always use Nz() function. Something like.

Me.Filter = "([Company].Value Like '*" & [COLOR=Red][B]Nz([/B][/COLOR]Me.Combo108[COLOR=Red][B], "")[/B][/COLOR] & "*') AND " & _
            "([Report_Type] = '" & [COLOR=Red][B]Nz([/B][/COLOR]Me.Combo123[COLOR=Red][B], "")[/B][/COLOR] & "') AND " & _
            "([Rep_Year] Between " & [COLOR=Red][B]Nz([/B][/COLOR]Me.Combo125[COLOR=Red][B], -365)[/B][/COLOR] & " AND " & [COLOR=Red][B]Nz([/B][/COLOR]Me.Combo127[COLOR=Red][B], 9999)[/B][/COLOR] & ")"
Me.FilterOn = True

 

[sagi871]

Thank you,
Didn't know this one exists. It works perfectly.

 

[pr2-eugin]

You are welcome ! Good luck.

 

[vbaInet]

#6: That blended in nicely with your post Paul. Almost got me confused.

 

[pr2-eugin]

#6: That blended in nicely with your post Paul. Almost got me confused.

Ha Ha ! Unfortunately we have to make it quiet obvious.