How to combine filters in VBA (1 Viewer)

droops14 · May 22, 2019

Hi all
I've a form which has four dropdown boxes. Based on the combination of the selection in those 4 fields, I want a combined filter to work.
You can easily use an AND statement to combine the filters but you end up with issues if one of the dropdown fields is left empty as the filter will look for null values for that variable and the filter doesn't work. I now solved this by creating iff statements and redefine the filter based on whether a value is completed or not but for 3 dropdown boxes this already gives me 8 different combinations possible so by adding this fourth dropdownbox, this is not feasible anymore, I hope you understand what I mean.
Is there no easier way to combine filters that work on op of each other instead of having to define iff statements?

example:
So if I have
dropdown 1 completed
dropdown 2 completed
dropdown 3 not completed (=NULL)
dropdown 4 completed

I want the code to filter on 1, 2 and 4 and not take into account 3.
Can I use 1 statement that takes all possible combinations into account?
Thanks for your help

Minty · May 22, 2019

I would have a look at the Allen Browne search example - it shows how to build a search form in a number of ways. http://allenbrowne.com/ser-62.html

His whole site is worth a good read !

isladogs · May 22, 2019

Also have a look at my two example apps on building a sql string for multiple filters.
http://www.mendipdatasystems.co.uk/multiple-group-filter/4594454290

MajP · May 22, 2019

Untested, but this is the approach I use for any amount of controls

Need a string variable for each control
Check if the control is null and make a filter for each
Combine the individual filters
remove the AND off the end

Code:

Public function GetFilter() as string
dim strFilter as string
dim filterOne as string
dim filterTwo as string
dim filterThree as string

if not isnull(combo1) then 'Text
  filterOne = "field1 = '" & me.combo1 & "' AND "
end if

if not isnull(combo2) then 'Numeric
  filterTwo = "field2 = " & me.combo2 & " AND "
end if

if not isnull(combo3) then  'Date
  filterThree = "field3 = #" & format(me.combo3, "mm/dd/yyyy") & " AND "
end if

strFilter = FilterOne & filterTwo & filterThree
'strip off the last AND

strFilter = left(strFilter, len(strFilter) - 4)
getFilter = strFilter
end function

ajcbutler · Jul 22, 2021

Hi. This would work great if all boxes have something in them but what is I had blank in Filterone, something in filtertwo and nothing in filterthree?

Gasman · Jul 22, 2021

It would work just as well in your scenario?

Try testing it?

ajcbutler · Jul 22, 2021

Sorry. I have tested and it does work. Awesome coding Gasman.

MarkK · Jul 22, 2021

You could economize on code a little if you use Nz() and an array, like...

Code:

Public Function GetFilter() As String
    Dim flt As String
    Dim flt_(2)
    
    flt_(0) = Nz("AND field1 = '" + Me.combo1 + "'")
    flt_(1) = Nz("AND field2 = " + Me.combo2)
    flt_(2) = Nz("AND field3 = #" + Format(Me.combo3, "dd-mmm-yy") + "#")
    
    flt = Join(flt_, "")
    If Len(flt) Then GetFilter = Mid(flt, 5)

End Function

Gasman · Jul 22, 2021

ajcbutler said:
Sorry. I have tested and it does work. Awesome coding Gasman.

I did not write it, MajP did ?

Pat Hartman · Jul 22, 2021

Or since you have only four options, you can just use a saved querydef. With more options or some required, some optional, I would use VBA to create the WHERE clause.

Select ... From yourtable WHERE
(Fld1 = Forms!yourform!fld1 OR Forms!yourformfld1 Is Null)
AND
(Fld2 = Forms!yourform!fld2 OR Forms!yourformfld2 Is Null)
AND
(Fld3 = Forms!yourform!fld3 OR Forms!yourformfld3 Is Null)
AND
(Fld4 = Forms!yourform!fld4 OR Forms!yourformfld4 Is Null);

Notice the pattern and you'll be able to use this technique elsewhere. The "AND" operators specify that ALL options must be true in order to select a row. However the "OR" operators will cause the second part of each expression to return true if the form field is empty. So
(fld1 = 876 OR True) results in True so the row will be selected

In order to make AND and OR operators to work correctly in the same expression, you will need to use parentheses to tell Access/SQL how to evaluate the expression. This is the same rules that apply in complex math expressions that make

1 + 5 * 6 = 31
and
(1 + 5) * 6 = 36

The parentheses control the order of operation. The default is multiplication/division take precedence over addition/subtraction so the first expression is evaluated as 1 + (5 * ^)

In logical expressions, AND takes prescience over OR. To enhance your understanding, change the parentheses of my WHERE clause to show how SQL would evaluate the expression without the help of the parentheses I used. Post here if you want to know if you got it right.

How to combine filters in VBA (1 Viewer)

droops14

New member

Minty

AWF VIP

isladogs

MVP / VIP

MajP

You've got your good things, and you've got mine.

ajcbutler

New member

Gasman

Enthusiastic Amateur

ajcbutler

New member

MarkK

bit cruncher

Gasman

Enthusiastic Amateur

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread