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
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
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 NextDim 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 IfMe!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.