Building a Dynamic Where Clause

September 8, 2009

One of the truly great features of Microsoft Access is the [WhereCondition] parameter of the OpenForm() and OpenReport() methods. Forms and reports are easily filtered by passing a Where clause as simple text to the Open method of a form or report. As shown in the screen shot, IntelliSense helps you see where to insert your Where clause when calling a form or report.

Click for larger image
IntelliSense helps you see where to insert your Where clause

The simple text passed to this parameter looks a lot like the Where clause of a query and must follow the familiar rules of SQL. If you’re comfortable writing your own SQL queries this isn’t a big deal but if you’re accustomed to using the graphical interface, you may find this part a bit challenging.

In this article, we explore some practical examples which demonstrate how you might go about creating dynamic Where clauses. I’ll also reveal some tricks for simplifying the process. The code in this article is available in the download and is generally pretty simple to implement. Let’s get started.

Where Condition Basics

The more complex the filter, the more likely you’re going to need some sort of user interface, where the user may describe the filter conditions but simple Where Conditions may be handled in-line. You might have a form that returns only Active Employees or a report that shows only Open Cases. Simple and unchanging conditions like this may be hard-coded into the call you use to open the report or form, as follows:

    DoCmd.OpenForm "frmEmployees", acNormal, "[Active]=True"
    DoCmd.OpenReport acViewPreview, "rptAbsence", "[Status]='Open'"

The last argument of the Open method is the WhereCondition. It is, effectively, the same text that could be added to the Where clause of a SQL query, only without the "WHERE" operator.

This is, of course, its simplest form. The most complex condition would probably cause this parameter to exceed its maximum size, which is 2048 characters. There are work-arounds for this size limit issue, but for the sake of this article, we'll focus where conditions that fit within the limitation. There's a Microsoft support article that describes the work-around and it may be found here: http://support.microsoft.com/kb/q184948/

Building the User Interface

The demo form I created isn't exactly pretty, but it does demonstrate some of the filter controls that could be implemented to generate a dynamic Where clause. They include the following:

  • List of available reports
  • List of available date fields with From and Thru date text boxes
  • Drop-down list that includes an <All> option
  • A multi-select List option
  • Text field with wild-card search
  • Filter on numeric value

Dynamic Where Clause Demo

Which controls you choose to implement will depend on your requirements and of course, you'll need to populate option lists from your own data sets, but here are a few things that might be useful. First, to get a list of reports, you could use this query as the RowSource for the drop down list:

    SELECT [Name] FROM MSysObjects WHERE [Type]=-32764 ORDER BY [Name];

For the [Select Date Field] list I used a Value List of known possibilities that exist in my set of tables. You may find that you have only one date field of consequence, which would make this approach overkill but over the years, it seems like there are always a lot of dates on which one may want to filter. That makes this is a good attribute for a dynamically selected field name.

The RowSource for the Select Employee list is somewhat more complicated. It employs a UNION query to add an <All Employees> option to the list. Here's the trick: Set the EmployeeID to a single-quoted asterisk (wild card character). This probably seems odd, mixing a string character with numeric IDs, but oddly enough, this works, as we'll see when we generate the Where clause below.

    SELECT "'*'" AS EmployeeID, "<All Employees>" AS Employee 
    FROM MSysObjects 
    UNION 
    SELECT EmployeeID, [LastName] & ", " & [FirstName] AS Employee 
    FROM Employees 
    ORDER BY Employee

Another trick in the above SQL is the use of the MSysObjects table from which the <All Employees> option is created. Access requires that a FROM clause exist (SQL Server doesn't) so you must pull from some table. The UNION suppresses duplicates, so this arrangement works, believe it or not. Some developers keep a dummy table around with a single record for this purpose.

There's nothing tricky about the other fields in the user interface piece. Now that we have something to work with, let's generate a Where clause.

Generate the Where Clause

There are a number of ways to proceed, and over the years, I suspect I've used them all. Below is the syntax I keep coming back to. My approach is to test each control to see if something was supplied, and if so, add it to the Where Clause string. If the Where Clause string already has some text in it, I append an " AND " clause to it. (You mustn't prefix the clause with WHERE but conditions must be separated with AND operators.)

([Condition1]='A') AND ([Condition2]='B') AND ([Condition1]='C')

The code block below is very specific to the data in my database. I have an Employee table with an [EmployeeID] field, a Customers table with a [Country] field, etc. Odds are, your database doesn't have these tables or fields, so you'll have to modify the code below to get it to work with your database. There isn't anything generic about the code, but the concept is reusable.

Private Sub cmdGenerateWhere_Click()
On Error Resume Next

    Dim iItem As Integer
    Dim sList As String
    Dim sWhere As String
    Dim sValue As String
    Dim sField As String
    Dim dFrom As Date
    Dim dThru As Date

For the first filter control, the user must select the field to which the date filter applies. Once selected, the Where clause will be created provided a From Date value has been supplied. If the Thru Date value is missing, it is assumed to be the same as From Date. Note too that dates in Microsoft Access are delimited with the pound sign (#) in SQL statements. For readability, I add a carriage return to the end of the string.

    ' DATE  CONDITION
    sField = Nz(Me!cboDateField, "<no date field>")
    If sField <> "<no date field>" Then
        If Nz(Me!txtFromDate, "") <> "" Then
            dFrom = Me!txtFromDate
            dThru = Nz(Me!txtThruDate, Me!txtFromDate)
            sWhere = " (" & sField & " BETWEEN #" & _
                       dFrom & "# AND #" & dThru & "#) "
            sWhere = sWhere & vbCrLf
        End If
    End If

The EmployeeID control allows for the "<All Employees>" option. When selected, the "EmployeeID" is not numeric, but becomes a quote delimited wildcard character, '*'. For this to work, the LIKE operator is used instead of the EQUALS sign. This works because as far as the Access Query Engine is concerned, [EmployeeID] LIKE 5 is the same as [EmployeeID] = 5, returning only 1 match. Also, [EmployeeID] LIKE '*' returns ALL employees, which is what we want. While this does work, large data sets might incur a performance hit.

    ' EMPLOYEE  CONDITION
    sValue = Nz(Me!cboEmployeeID, "")
    If sValue <> "" Then
        If Len(sWhere) > 0 Then sWhere = sWhere & " AND "
        sWhere = sWhere & " ([EmployeeID] LIKE " & sValue & ") "
        sWhere = sWhere & vbCrLf
    End If

The next control is the Multi-Select List Box. To create a Where clause, we will use the IN operator and create a coma-delimited list of selected countries. This is the option that could, if not watched, exceed the 2048 character limit should a user select 100 countries from the list.

    ' COUNTRY CONDITION
    For iItem = 0 To lstCustomerCountry.ListCount - 1
        If lstCustomerCountry.Selected(iItem) = True Then
            sValue = lstCustomerCountry.Column(0, iItem)
            sList = sList & ",'" & sValue & "'"
        End If
    Next
    If Len(sList) > 1 Then sList = Mid(sList, 2)
    
    If sList <> "" Then
        If Len(sWhere) > 0 Then sWhere = sWhere & " AND "
        sWhere = sWhere & " ([Country] IN (" & sList & ")) "
        sWhere = sWhere & vbCrLf
    End If

The wild-card search on free text is pretty straight-forward, with one trick. Since the values will be delimited with single quotes, you must double the apostrophes found within the text so they don't break the SQL. So, the text Grandma's Kitchen becomes Grandma''s Kitchen and the final WHERE clause would look something like this:

   
[ProductName] LIKE '*Grandma''s Kitchen*'

To double the single quotes, use the Replace() function, as shown below.

    ' PRODUCT NAME CONDITION
    If Nz(Me!txtProductName, "") <> "" Then
        If Len(sWhere) > 0 Then sWhere = sWhere & " AND "
        sValue = Replace(Me!txtProductName, "'", "''")
        sWhere = sWhere & " ([ProductName] LIKE '*" & sValue & "*') "
        sWhere = sWhere & vbCrLf
    End If   

The last control is numeric, and there's very little about it that is unusual, except that numbers don't require any delimiter at all. In fact, if you were to put quotes around it, the SQL would probably fail. In this case, we are looking for UnitPrice values that are greater than or equal to the user supplied value.

    ' UNIT PRICE CONDITION
    If IsNumeric(Me!txtUnitPrice) Then
        If Len(sWhere) > 0 Then sWhere = sWhere & " AND "
        sValue = CStr(Format(Me!txtUnitPrice, "0.00"))
        sWhere = sWhere & " ([UnitPrice] >= " & sValue & ") "
        sWhere = sWhere & vbCrLf
    End If
    
    Me!txtWhereClause = sWhere

End Sub

Finish the Job

We're almost done. All that's left to do is pass the dynamically generated Where condition to the report as we open it. An example would look like this:

    strCriteria = Nz(Me!txtWhereClause, "")
    DoCmd.OpenReport "rptEmployees", acViewPreview, , strCriteria

Here's where the problems begin, especially with my demo application. The thing is that not every report of mine has an [EmployeeID] field. Not every report has a [Country] field. If and when missing parameters are passed to a form or report in a WhereCondition parameter, you get error messages like this:

missing parameters result in an error

So as you can see, the above dynamic where generation code is a good start, but there's a ton of customization ahead of you. The Holy Grail of reporting is a generic reporting engine, but a dozen years of programming hasn't gotten me any closer. What we've demonstrated here is a flexible, reusable way to create report (and form) filters, but in the end, the filter mechanism must know what attributes the report exposes.

Back in one of my 2003 articles I mentioned a more complicated report filter application I had built and it's included in the download for that article. It's still posted on Database Journal and if you're ready to take this process to the next step, you may want to check out the download that comes with that article: Access Report Tricks.

» See All Articles by Columnist Danny J. Lesandrini








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers