How to combine filters in VBA

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 ?

How to combine filters in VBA

droops14

New member

Minty

AWF VIP

isladogs

Access 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

Similar threads

Users who are viewing this thread