<

Building a Custom Filter

Published on
21,825 Points
5,025 Views
8 Endorsements
Last Modified:
Awarded
In the previous article, Using a Critera Form to Filter Records, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain open, and the data was never actually transformed into a filter.

In this article, Visual Basic code is used to build a filter as a string. It can be used to filter forms and reports, to search for records, or even to build dynamic SQL.

If the techniques shown here are not sufficient, more advanced solutions are discussed in the third article of this series, Custom Filters using BuildCriteria.

____________________

The demo database is the same for all three articles. It contains tables from the well know Northwind Traders database, from a rather old version, as sample data. The forms are all variations on the common theme custom filtering.
FormFilters.mdb



Why build a filter


A filter is an expression, which can become surprisingly complex, specifying which records should be included in a given context. Is is often described as a WHERE clause, without the word “where”. This means that the language used is SQL, or rather the SQL expression syntax. For example:
OrderID=10042
OrderDate>=#2011-01-01#
ShippedDate Is Null
Not Discontinued And UnitsOnOrder>0 And ProductName Like '*tofu*'
ShipCity='Paris' Or ShipCity='Reims'
CategoryID In (2,3,5)
UnitPrice Between 100 And 200
Salary > (Select Avg(Salary) From Employees)
OrderID In (Select OrderID From [Order Details] Where ProductID=12)
A filter can also contain function calls (logical functions like IIf or Switch, text functions like InStr or Mid, date functions, etc.). Note that the examples above all follow a pattern similar to

[Not] ‹field› ‹operator› ‹expression [And|Or ‹field› ‹operator› ‹expression› [...]]
The simple expressions, sometimes called predicates are linked with boolean operators. Brackets might be needed when mixing “And” and “Or”, and some predicates have a special syntax like “Discontinued” (a ‹boolean› alone), “Between... And...”, or “‹field› Is Not Null” (instead of “Not ‹field› Is Null”).

The important point is that the fields are not embedded in an expression. This allows the database engine to optimize the query for example by using index searches and boolean searches, which is much faster then reading all the records.

Typical uses are:
    DoCmd.OpenReport "‹form name›", WhereCondition:=‹filter›
    
    With Forms("‹form name›")
        .Filter = ‹filter›
        .FilterOn = True
    End With

    With CurrentDb.OpenRecordset("‹table/query›")
        .FindFirst ‹filter›
        If .NoMatch Then MsgBox "not found!"
    End With
    
    strSQL _
        = " UPDATE Products" _
        & " SET UnitPrice = Round(UnitPrice * 1.1, 2)" _
        & " WHERE " & ‹filter›
    CurrentDb.Execute strSQL

Open in new window

It should be clear by now that two languages will be used: SQL expressions and Visual Basic code. What's more, we will use one language to create an expression for another, something I find both philosophically intruiguing and technically challenging.

Visual Basic expressions and SQL expressions are very similar in many regards, but they are not identical. Two examples: SQL recognizes both double and single quotes to delimit string literals while VB only uses double quotes; SQL undstands “‹expr› Is Null” while VB needs “IsNull(‹expr›)” (which can be used in SQL as an external function call as well, but is much slower than the native syntax)



The Filter Creation Form

filter form with current filter displayedThe selections made by the user in the various controls are translated into a valid filter, displayed at the bottom. This form opens a report, passing the filter in the `where condition´ argument, but the same filter could be used for many other things.

The code building the filter follows a very simple pattern:
    Dim Where As Variant
    
    Where = Null
    If Not IsNull(‹control1›) Then Where = Where + " AND " & ‹expression›
    If Not IsNull(‹control2›) Then Where = Where + " AND " & ‹expression›
    ' ...

Open in new window

The use of “+” as operator ensures that “AND” is inserted only between predicates. Other solutions work just as well, such as:
    Dim strWhere As String
    
    If Not IsNull(‹control1›) Then strWhere = strWhere & " AND " & ‹expression›
    If Not IsNull(‹control2›) Then strWhere = strWhere & " AND " & ‹expression›
    ' ...

    ' remove the first " AND "
    If Len(strWhere) Then strWhere = Mid(strWhere, 6)

Open in new window

The result is similar: the complete criteria links all predicates based on individual controls. For most controls, the expression is very simple.
    If Not IsNull(cboSupplier) Then _
        Where = Where + " AND " _
            & "SupplierID=" & cboSupplier

Open in new window

This is a sinlge line, like in the code above, broken with line-separator characters. I find it slightly more readable, especially in a long list of similar code fragments, but it is of course not required.

Direct boolean predicates don't need an operator:
    If Not IsNull(chkDiscontinued) Then _
        Where = Where + " AND " _
            & IIf(chkDiscontinued, "Discontinued", "Not Discontinued")

Open in new window

Access will often write instead “Discontinued<>False”, and you can also treat yes/no fields just like any other field: “Discontinued=True”.

The multi-select list box of categories is best translated using “In (...)”. The algorithm to create the list of numbers is the same, incorporated into a loop over all selected items.
    With lstCategories
        Dim Item As Variant
        Dim List As Variant
        If .ItemsSelected.Count Then
            List = Null
            For Each Item In .ItemsSelected
                List = List + "," & .ItemData(Item)
            Next Item
            Where = Where + " AND " & "CategoryID In (" & List & ")"
        End If
    End With

Open in new window

The form also shows one example of a predefined criteria, offered to the user in an option group. This is a good way to incorporate special demands made by the users.
    Select Case grpOrders
    Case 1: Where = Where + " AND " & "UnitsOnOrder>0"
    Case 2: Where = Where + " AND " & "UnitsOnOrder=0"
    End Select

Open in new window

The same method can be used to add any number of complex filters like mutli-field predicates or conditional expressions using Switch(), for example.

All the examples so far were quite straightforward, and used only the most elementary programming techniques. The first tricky problem arises when handling free text entered by the user. The obvious simple solution is
    If Not IsNull(txtProductName) Then _
        Where = Where + " AND " _
            & "ProductName Like '*" & txtProductName & "*'"

Open in new window

But when the user searches for products containing the string “Anton's”, the resulting predicate becomes:

ProductName Like '*Anton's*'
The trailing “s*'” cause a syntax error. We can try using double-quotes and get a away with it for a while, but it isn't a real solution. If single quotes need to be embedded in a single-quoted string, one can use double-single-quotes (huh?). It's easier to show that to explain:

ProductName Like '*Anton''s*'
This doesn't cause and error and correctly finds for example “Chef Anton's Gumbo Mix”. The code to obtain this predicate is:
    If Not IsNull(txtProductName) Then _
        Where = Where + " AND " _
            & "ProductName Like '*" & Replace(txtProductName, "'", "''") & "*'"

Open in new window

When you need to do this often, it's very useful to create a global function, I call it QuoteSQL, that will add the quotes while handling the embedded quotes. It can be used like this:
    If Not IsNull(txtPackaging) Then _
        Where = Where + " AND " _
            & "QuantityPerUnit Like " & QuoteSQL("*" & txtPackaging & "*")

Open in new window

The demo database contains the form in the figure, and the function BuildFilter from which the samples above have been extracted. These are the global functions QuoteSQL and DateSQL:
Function QuoteSQL(Text) As String
' quotes the passed text for SQL, handling embeded single quotes
    
    If IsNull(Text) Then
        QuoteSQL = "Null"
    Else
        QuoteSQL = "'" & Replace(Text, "''", "'") & "'"
    End If
    
End Function

Function DateSQL(AnyDate) As String
' formats the passed date as ISO format, with surrounding # separators
    
    If IsNull(AnyDate) Then
        DateSQL = "Null"
    Else
        DateSQL = Format(AnyDate, "\#yyyy\-mm\-dd\#")
    End If
    
End Function

Open in new window

You can use DateSQL to create predicates that will no break on a computer with different local date settings. Americans often use this shortcut:
    ... "SomeDate=#" & txtDateControl & "#"

Open in new window

This will work reliably only on computers using US dates or ISO dates (the two formats understood by Jet SQL), but will give uncoherent results on all others.

By now you already have enough information to solve even relatively complex cases. The next section covers some additional topics, corresponding to much less frequent needs.



Advanced Topics in Building Filters

filter form with dates and filter descriptionFor a change, this filter is meant for the Orders table. The upper three controls are managed just like in the previous section. Two date fields have been added, as an illustration of date formatting and of a predicate using two values. The bottom two combo boxes demonstrate advanced multi-table filtering: the table itself doesn't contain any information about products, let alone product categories. Finally, the box at the bottom shows a human-readable version of the current filter, instead of the SQL filter.


Date Between... And...
    If IsNull(txtFromDate) Then
        If IsNull(txtToDate) Then
            ' no filter...
        Else
            Where = Where + " AND " _
                & "OrderDate<=" & DateSQL(txtToDate)
        End If
    Else
        If IsNull(txtToDate) Then
            Where = Where + " AND " _
                & "OrderDate>=" & DateSQL(txtFromDate)
        Else
            Where = Where + " AND " _
                & "OrderDate Between " & DateSQL(txtFromDate) _
                & " And " & DateSQL(txtToDate)
        End If
    End If

Open in new window

Depending on which dates have been entered, the predicate will use “<=”, “>=”, or the “Between... And...” construct. In old versions of Access, the documentation recommended using this construct when possible, but this is no longer the case. I still find this syntax more readable, however. Note the use of DateSQL, creating a string like #2011-06-07# for the date of writing.


Multi-Table Criteria
    If Not IsNull(cboProduct) Then _
        Where = Where + " AND " _
            & "OrderID In (" _
            & "  Select OrderID" _
            & "  From [Order Details]" _
            & "  Where ProductID=" & cboProduct _
            & "  )"

Open in new window

The filter uses a subquery, which reads something like “the order ID must be in the list of order IDs from order details for product X”, meaning “show only orders having product X”. Whether this is obvious or not to the user of the form depends on two things: what is the user-perceived data model (in this case: are orders and their details perceived as a single object? — probably yes) and how is the criteria presented on the form (the label “product” is not sufficient, it should read “orders having product” at least).

The second combo uses even more remote information, and the label should definitely be more explicit, e.g. “orders with at least one product from category:”. The resulting predicate is

OrderID In (
  Select D.OrderID
  From
    [Order Details] D Inner Join
    Products P On D.ProductID=P.ProductID
  Where P.CategoryID=‹number›
  )
The ability to filter one table based on linked data in other tables is an essential tool in many situations. The user could be trained to create advanced filters and write subqueries but that would still be a lengthy and cumbersome task. If it makes sense to filter orders by the products concerned, and if that is needed often, a pop-up form creating that filter will be very welcome.


Human-readable explanation
complex filter, hard to readThe filter has become rather technical, and practically useless in keeping the user informed and for documenting any report based on in. The human-readable version displayed in figure 2 is obtained by a parallel function creating the explanation in exactly the same way the filter was created.
    If IsNull(txtFromDate) Then
        If Not IsNull(txtToDate) Then
            Criteria = Criteria + " • " & "Ordered up to " & txtToDate
        End If
    Else
        If IsNull(txtToDate) Then
            Criteria = Criteria + " • " & "Ordered since " & txtFromDate
        Else
            Criteria = Criteria + " • " & "Ordered between " & txtFromDate & " and " & txtToDate
        End If
    End If

Open in new window

Writing this function is very simple and quite fast. The problem is rather one of maintenance: it isn't always easy to remember to propagate a change made in the criteria building function to the explanations function.

Both the filter and the explanation can be passed to a report with
    DoCmd.OpenReport "rptOrders", _
        View:=acViewPreview, _
        WhereCondition:=BuildFilter(), _
        OpenArgs:=ExplainFilter()

Open in new window

and the report can use it in a report header or footer like this:

=Nz('Filtered • '+Report.OpenArgs,'(unfiltered)') & Format(Count(*),'" — "0" orders"')
The expression works well as subtitle for the report title, and it makes it immediately visible that only a subset of the orders are being included in the list.



Saving Filters


If the form grows long and complicated, and if the users start spending minutes carefully designing specific filters, it might be a good idea to incorporate a save mechanism. This is relatively simple, and several solutions exist.

The simplest is to create a table with one field for each control on the filter building list. This table can be bound to the form and the navigation buttons reactivated. The filter immediately becomes a list of filters, automatically saved. However, this isn't the expected interface for such objects.

Instead, the form can remain unbound, but with two buttons and a combo box to save, delete, and load filters. I haven't incorporated this mechanism in any of the filter forms of the attached database, but a simple demo form is provided with just such buttons and combo. It should be easy to adapt to any actual filter form.



Conclusion


Filters are used in so many places and are so vital to the users that it makes perfect sense to provide tools to build them quickly and reliably. A filter building form is also an interesting place, because it creates the bridge between the internal data structure (dictated by normalization and optimization) and the user-perceived object model (conditioned by work processes and business models). Although the examples here were all pop-up forms, the same filter building techniques can be used directly on the form being filtered or searched; this might fit better with the user's navigation methods.

A filter building form is necessarily less versatile than, say, the query design grid, let alone free text SQL queries. However, it is almost always superior in efficiency and comfort, when it anticipates the most frequent needs of the users and presents the fields in a familiar way.

Still, wouldn't it be nice if the same control could be used to specify all sorts of predicates? Can't we let the user type “2010” to choose a year, and also “>=2010”, “Not 2010”, or simply “Null”? This possibility is explored in the next article.


Meanwhile, I hope you are now able to create good looking and efficient filter building forms; you should rapidly find more and more places where such forms can improve your applications. With a little practice, they take very little development time, so... If it takes one hour to create saves two minutes a day for three users: how soon will the balance be positive?

Finally, I would like to thank SowleMan, who kindly offered his time and his experience in writing system documentation, and carefully read and corrected my first draft. His work and his suggestions are appreciated, with gratitude by the author and unknowingly by the readers.


Markus G Fischer
(°v°)

¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
This article is part of the EE-book unrestricted Access.
8
Comment
Author:harfang
1 Comment
 

Expert Comment

by:developingprogrammer
thanks harfang for the fantastic article!

just one quick point (for myself and also other who may not be very proficient in programming like me) -

The use of “+” as operator ensures that “AND” is inserted only between predicates. Other solutions work just as well, such as:

using the + to concatenate the string ensures that if there is a null within the string, the entire string becomes a null. in this case there is a null in the string because the Where is set to null at the start

Dim Where As Variant
   
    Where = Null

hope this helps! = )
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month