I’m often asked by clients to perform combination filters. That is, not just a single filter on a form. But combining multiple filters that interact with one another.
For instance, in the Marketing Database I’ve built for datacontrolllc.com, I have an email blast functionality that allows me to send bulk emails to my existing clients. I use this to offer monthly discounts for various services. On this email blast form I’ve got a couple of filters that I’d like to use in combination with one another.
On this form I have two filters, both highlighted in green (our company’s convention for filters). I’ve filtered the form by a single filter, the name/email filter using a wildcard filter of ‘data’ that targets, firstname, lastname and email. Notice I’ve not used the second filter, email sequence.
After typing in a value in the name/email filter, the database automatically filters the form. I approach this with an [EventProcedure] on the after update Event for each of the filter Controls.
Notice that I’ve taken the time to name this control: ‘txtFiltername’. Naming this control in such a way will help me when I write code for this control, and then year’s later when I might modify the code, the name of ‘txtFiltername’ will make a lot more sense to me than ‘text18’
Here’s the code that performs the combination filter:
Email me at ‘jjaeger@datacontrolllc.com’ and I’ll email back this code so you don’t have to copy it.
The first two sub procedures: ‘txtFilterName_AfterUpdate’ and ‘frmFilterEmailSequence’ are the Event Procedures from the two filter controls. These sub procedures simply call another sub procedure: subFilter.
The sub procedure ‘subFilter’ does all of the work. Let’s start with the end of the sub procedure:
Me.FilterOn = False
Me.Filter = strFilter
Me.FilterOn = True
This is how you get a form to filter using code. First you clear the current filter, then you give the filter property of the form a value (in this case a string variable: ‘strFilter’) and then you turn the filter on.
Now let’s take a look at the beginning of the procedure:
Dim strFilter As String
strFilter = “”
Here we establish the strFilter variable and give it a default value: ”
If Not IsNull(frmFilterEmailSequence) Then strFilter = ” AND frmFilterEmailSequence=” & Frame28
If Not IsNull(txtFiltername) Then strFilter = strFilter & ” and (ContactLName like ‘*” & txtFiltername & “*’ OR ContactfName like ‘*” & txtFiltername & “*’ OR Email like ‘*” & txtFiltername & “*’)”
Here we give ‘strFilter’ a value by inspecting our two filter controls. Notice that put an ‘ AND ‘ statement in front of both strFilter values.
Lastly, we expect to have ‘ AND ‘ in front of our filter so remove the and with a ‘RIGHT’ function:
If strFilter <> “” Then strFilter = Right(strFilter, Len(strFilter) – 5)
Notice that trick we use in the txtFiltername section of code. We use ‘(‘ and ‘)’ to combine OR statements and we use ‘Like’ and ‘*’ operators to perform wildcard functions.