Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

MS Access

Posted Nov 21, 2003

Access Report Tricks - Page 2

By Danny Lesandrini

Filtering and Grouping

Eventually, you are going to need to be able to dynamically change the content or format of the data contained in reports.  While your needs may vary, here are some common methods for accomplishing simple filtering and grouping.


The simplest way to filter for a single parameter is to put a criteria expression in the query, like this:

    SELECT * FROM tblDownloads WHERE tblDownloads.Type = [Enter Type of Download] 

The problem with this approach is that your users need to know what "types" are available. Wouldn't it be nicer to offer them a drop-down list of options? You can easily do this by creating a form as shown in the image below, but then your query must be modified to reference this control value, as follows:

  SELECT * FROM tblDownloads WHERE tblDownloads.Type = Forms!frmFilter!cboType 
Criteria collection form

This form is included in the download for this article, so you can examine it yourself, but it has a combo box that looks up valid Type values so that users can simply select a valid value. It stores that value in the control named "cboType".

On the other hand, how do you get this form to "pop up" at the right moment? Again, there are various ways to accomplish that, but here's a very simple approach:  Load the form in the Report's Report_Open() event. The trick necessary to make this process work is that you open the form in Dialog mode.

In Dialog mode, the form halts all other processing until it is either closed or hidden. In our case, we cannot close it or we will lose the value of cboType, so the OK button simply hides the form, which allows the report to continue opening. As it does, its RecordSource, our query described above, is filtered for the selected type. The code is simple, and looks like this:

  Private Sub Report_Open(Cancel As Integer)
       On Error Resume Next)
         DoCmd.OpenForm "frmFilter", , , , , acDialog)
       End Sub


If you want to filter more than one field, then simply add combo boxes to the filter form and additional parameter criteria to the query. You can even pass sorting information, as shown in the image below of a much more complicated filter criteria form. The code behind this example is too complicated to describe here, but you may examine it yourself by downloading the Report Filter Example code file.

Report filter example


One last trick: Dynamic Grouping. The idea here is to create a report with detail records and group totals but determine at run-time whether or not you want to display the detail lines or not.

We use the same method as above, but instead of altering a query, we add code to the Report_Open() event to show or hide the detail section. In the example below, I have renamed the detail section to Detail_001 (by default, it is named "Detail"). This is just to illustrate that each report section is really just a control, whose properties you can modify. Notice the code required to hide the detail section:

Criteria collection form

        Private Sub Report_Open(Cancel As Integer)
       On Error Resume Next

         DoCmd.OpenForm "frmOption", , , , , acDialog

         blnHideDetail = CBool(Nz(Forms!frmOption.cboType, False))

         If blnHideDetail Then
           GroupFooter1.Height = 315
           Detail_001.Visible = False
         End If

       End Sub


So, based on the user's selection, the detail section is either left untouched (visible) or is hidden. The result is a report with group totals only.  You now have effectively delivered two reports to your users:  one with detail records and one without.  I've saved the report output as snapshot files, which you can examine for yourself:

Detail-Summary Example with detail records (Requires the Snapshot Viewer)
Detail-Summary Example without details (Requires the Snapshot Viewer)


There is just so very much you can do with Access reports. What I have shown here only scratches the surface and your current needs may vary, but the ideas described in this article and the associated code will get you started. The rest is limited only by your imagination. All the code for this article is available below. Download it, play around with it, and soon you will come up with some tricks of your own.

Report Tricks Code
Report Filter Example

» See All Articles by Columnist Danny J. Lesandrini

MS Access Archives

Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM