Building a Custom Filter

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:
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
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

Select all 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

The 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›
                          ' ...

Select all 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)

Select all 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

Select all 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")

Select all 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

Select all 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

Select all 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 & "*'"

Select all Open in new window

But when the user searches for products containing the string “Anton's”, the resulting predicate becomes:
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:
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, "'", "''") & "*'"

Select all 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 & "*")

Select all 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

Select all 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 & "#"

Select all 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

For 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

Select all 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 _
                                  & "  )"

Select all 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
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
The 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

Select all 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()

Select all Open in new window

and the report can use it in a report header or footer like this:
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.