Combination Form Filters

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.

Combination Filters - Email Blast Form

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.

Combination Filters - Filter control properties

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:

Combination Filters - Code

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.

 

This entry was posted in Access Code Examples, Access Database Advanced Forms, Access Database Filters. Bookmark the permalink.