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.
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 youre comfortable writing your own SQL queries this isnt a big deal but if youre 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. Ill 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. Lets get started.
Where Condition Basics
The more complex the filter, the more likely youre 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:
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:
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.