How to Filter Reports in Microsoft Access

In yesterday's article, I provided 18 tips for building a great report filtering user experience (UX):

This is one of those topics that involves strong personal preferences among Access developers.  There are a handful of approaches that I believe are relatively common among professional developers.

Report Filtering UX Approaches

Here's a very brief list.  It is by no means exhaustive and is heavily constrained by my own imagination.

• One Form to Rule Them All: A single report preview form that includes every possible filtering criteria and can be used with all or most of the reports in an application.
• SQL WHERE Clause Builder: A form that provides users a way to build an arbitrary SQL WHERE clause.  Requires users to have a strong understanding of relational database theory, but can be very powerful for the right audience.
• Reusable "Get Data" Forms: A handful of forms where each form is often shared with a handful of reports.  One form might get two dates, while another form gets one date and a CustomerID.  The forms are opened in dialog mode, the user inputs get saved to a hidden global form, and the report RecordSources rely on global form values to provide the filtering.
• Query Parameter Prompts: You know those "Enter Parameter Value" input boxes that pop up when you have a typo in a report's RecordSource?  You can do that on purpose and use that as a way to filter your reports.  It's a terrible UX, but–technically speaking–it can work.
• Report-Specific Filtering Forms: Each filterable report has its own associated filtering form.  This approach requires the most form objects, but provides the most flexibility from a design perspective.  Maintaining consistency across an application is also a challenge.

I'm sure there are other techniques that I'm leaving off, but that should provide sufficient context for our purposes here.

Report-Specific Filtering Forms

My personal preferred approach is to create report-specific filtering forms.

Here's an example for an ersatz Invoice Report:

The nice thing about this approach is that all the code related to building the report filter and previewing the report is encapsulated within the form's code-behind module.

Here are some notes about the above form:

• The form is unbound
• The labels (Segoe UI) and button captions (Verdana) use a sans serif font, while the text boxes and combo box use a monospace font (Consolas)
• The tab order is set to Start Date > End Date > Vendor > Preview Report > Close
• There is no input mask for the Start Date or End Date
• The Format property for Start Date and End Date is "Short Date"
• All three fields are optional and are clearly marked as such
• The record selector, navigation buttons, and scroll bars are all turned off
• The control box, min/max buttons, and close button are also all turned off
• The background and buttons use a custom color scheme that is consistent throughout my applications
• The labels are associated with the text boxes and combo box, so clicking on a label will set focus to the associated text/combo box
• There are ampersands before the "S", "E", and "V" in the captions of the labels, so pressing [Alt]+[S] or [Alt]+[E] or [Alt]+[V] will set focus to the Start Date, End Date, or Vendor control, respectively
• There are ampersands before the "P" and "C" in the captions of the buttons, so pressing [Alt]+[P] or [Alt]+[C] will preview the report or close the form, respectively

To maintain consistency within my applications, I generate a skeleton of these forms using my DesignNewForm() method, which sets several of the properties I mentioned above.

Building the WHERE Clause

I wrote a custom PreviewReport() function that I use in place of the standard DoCmd.OpenReport method.  

The second argument in the PreviewReport() function is a WHERE clause that gets applied to the report when it is opened.

Building the WHERE clause is an interesting exercise in and of itself.  In my example form above, all three of the controls are optional.  We need to handle the cases where the user provides zero, one, two, or three conditions.  This type of string building often leaves a dangling " AND " string that needs to be stripped off when we're done building our WHERE clause.  

My Conc() function obviates the need for such string stripping.

I used to require either both or neither dates when prompting for a date range in a report filter, but I found that making each date optional on its own had a few benefits:

• The code is simpler (as we'll see below)
• The filtering is more powerful and efficient (users can easily show all records before a given date without having to provide some sufficiently early starting date)
• The user interface is easier to understand and more intuitive

The Actual Code

Here's what the code looks like:

Private Sub btnPreviewRpt_Click()
    Dim Where As String
    
    If Not IsNull(Me.tbStartDate) Then Where = Conc(Where, "CreatedOn >= " & Dt(Me.tbStartDate), " AND ")
    If Not IsNull(Me.tbEndDate) Then Where = Conc(Where, "CreatedOn < " & Dt(Me.tbEndDate + 1), " AND ")
    If Not IsNull(Me.cbVendorID) Then Where = Conc(Where, "VendorID = " & Me.cbVendorID, " AND ")
       
    PreviewReport "InvoiceRpt", Where
End Sub

If we were to execute the above code using the values in the screenshot from above, this is what the contents of the Where variable would look like:

Here are a few notes about the above code:

• Each control is responsible for one condition of the WHERE clause and requires only one line of code
• There is no need to strip a trailing " AND " string from the Where variable
• I use my Dt() convenience function to wrap the literal dates in # signs
• The code works no matter how many conditions the user provides; passing a zero-length string to the Where parameter runs the report without any filtering
• Adding a new condition in the future requires only a single new control and a single new line of code
• Instead of using the BETWEEN statement, the date filters use greater than/less than signs so that they work properly regardless of whether the stored date data includes time values
• The two date text boxes have no input mask defined, but their Format properties are set to "Short Date" which is enough to provide date input validation
• If the user enters a Start Date that is later than the End Date, the report will return no records; I don't bother telling the user they're an idiot warning the user about their mistake, I let the empty report do the talking (by the way, my InformNoData() function shows a friendly message if the report is filtered to return no data and the PreviewReport() function prevents showing the "OpenReport action was canceled" error message)