Filters

Reports are all very well, but their details are fixed at design time. What you really need to be able to do is allow users a little degree of customization, and this is where filtering comes in. A report has two properties to help with this:

• Filter , which holds the details of the filter, usually a SQL statement

• FilterOn , which indicates whether the filter is on or off

Generally there are a couple of ways of visibly interacting with a report to filter data: we can set a criteria parameter in the underlying query or we can develop a filter form. If we want a 'quick 'n' dirty' solution then we may opt - while in query design mode - to place a parameter in the criteria section of the appropriate field of the query grid (for example, place ["Enter Company Name "] in the criteria of the CompanyName field in qrySalesSummary ) and when we try to preview the report, Access will automatically show an input box asking for a parameter, and by entering a valid company name we get a filtered report. However, if we want a professional result and one that offers better management of user input (for example, a list of valid input choices in a combo box) then we would opt for the filter form - this is the option that we will illustrate now.

What we are going to do next is create a form that gets shown when the report is opened. This will allow us to pick an Order Date, and whether to only show records that match, or are greater than or less than this date. A couple of buttons on the form will apply and clear the filter.

We'll use the same report we've been using throughout this chapter, so you might like to remove the duplicate formatting we did earlier. We took off the ones we added later, using conditional formatting, and kept the code versions. This is quite a long Try It Out , but it's fairly easy. A note of caution: some developers have experienced inefficiencies in using filters, so see the chapter on Data Management Techniques where we discuss alternatives.

Try It Out-Adding a Filter Form

1. Switch the Sales By Suppiler By Month form back into design view and add a new textbox to the Report Header section, like so:

   

2. Name the textbox txtFilter , and the label lblFilter and change the caption in lblFilter to 'Filtered:' . Set the Visible property for this textbox and its label to No .

3. Switch to the VBE, and in the NoData event for the report add the following code:

      MsgBox "There were no records for your selection."     Cancel = True   

4. In the Open event for the report add the following code:

      DoCmd.OpenForm "frmReportFilter"   

5. In the Close event for the report add the following code:

      DoCmd.Close acForm, "frmReportFilter", acSaveNo   

6. Now switch back to Access and close the report, making sure you save your changes. Create a new form and lay it out like this:

   

7. Name the combo box cboOperator , and set the Row Source Type property to Value List. In the Row Source property, type the following:

    =;>;<;>=;<= 

   These are the operators which the user will use to search for dates.

8. Name the text box txtValue , and set the Format property to be Short Date . Now set the Input Mask property to the following:

    99/99/00;0;_ 

9. Name the Apply button cmdApply , and the Clear button cmdClear .

10. Press the code button on the toolbar to create a code module for the form. This will switch you to the VBE. Now add the following variable declaration just after the Option Explicit statement:

     Dim m_rptSales As Report 

11. We're now going to code the various events for the filter form. Add the following line of code in the Form_Load event:

       Set m_rptSales = Reports("Sales By Supplier By Month")   

    Now, in the cmdClear_Click event, add the following code:

       txtValue = ""     With m_rptSales     .Filter = ""     .FilterOn = False     txtFilter.Visible = False     lblFilter.Visible = False     End With   

    Finally, in the cmdApply_Click event, add the following code:

       Dim strWhere As String     strWhere = "[DateOrdered] " & _     cboOperator & _     " #" & txtValue & "#"     With m_rptSales     .Filter = strWhere     .FilterOn = True     .txtFilter.Visible = True     .txtFilter = strWhere     .lblFilter.Visible = True     End With   

12. Save all of your changes, and close the new form. Now open the report ( Sales By Supplier By Month ) in Preview mode. Notice that the form is displayed too - it may be hidden by the report, so you might have to move it around on the screen (or find it via the menu: Window Sales Report Filter ).

13. Select the = sign from the operator combo box and add 07/20/02 to the textbox. Now press the Apply button.

    

    Notice how only those orders with an order date of 7/20/02 are shown.

14. Press the Clear button, and all of the records are restored.

How It Works

Let's start with the code in the report first.

In the NoData event we just want to inform the user that their selection didn't produce any records. We set Cancel to True to cancel the opening of the report.

 MsgBox "There were no records for your selection."  & _   vbCr & "Please try again." Cancel = True 

When the report is opened, the Open event is generated, and here we just make sure the filter form is opened at the same time.

 DoCmd.OpenForm "frmReportFilter" 

When the report is closed, the Close event is generated. There's no point keeping the filter form open once the report is closed, so we close the form.

 DoCmd.Close acForm, "frmReportFilter", acSaveNo 

That's all the code in the report. Now on to the filter form.

First, the variable declaration. Since we are going to be referring to an active report several times, we declare a form-level variable to hold a reference to the report. The variable has form-level scope because we placed it in the General Declarations section of the form code module.

 Dim m_rptSales As Report 

When the form ( frmReportsFilter ) is opened, we want to set this variable to point to the currently active report, so we have some code in the Open event for the form. We know what the report is called, so we use the report name to index into the Reports collection. Remember the Reports collection holds a list of all reports and can access the ActiveReport property. Once the variable is set we'll be able to use the variable to reference the currently active report, rather than using the collection. This is not only easier to read, but faster too (notice that we use the Set keyword because the report ( Sales By Supplier By Month ) is an object).

 Set m_rptSales = Reports("Sales By Supplier By Month") 

When the form is closing, the Close event is triggered, so we just set the variable to Nothing to clear the memory associated with it.

 Set m_rptSales = Nothing 

Now we need to look at what happens when we click the Apply button. The first thing we do is declare a string variable to hold the filter. This is a standard SQL WHERE clause, but without the WHERE .

 Dim strWhere As String 

To build this filter, we need the field we are filtering on, which is DateOrdered , followed by the operator, followed by the date. Notice that # signs have been put around txtValue , to tell VBA that the string is in fact a date:

 strWhere = "[DateOrdered] " & _   cboOperator & _   " #" & txtValue & "#" 

This gives us something like this:

 [DateOrdered] = #7/20/02# 

Now we have a filter statement, we need to set it on the report. We use the With construct here to save a bit of typing (it's more efficient, too).

 With m_rptSales 

We set the Filter property of the report to the filter statement we've constructed and then set the FilterOn property to True , indicating that a filter is in action.

 .Filter = strWhere   .FilterOn = True 

Since it's always a bad idea to show a filtered report without indicating that not all records are present, we set the textbox and label to indicate what the filter is. Otherwise, you'd give the user the impression that all records were shown, and this could have implications if the report is used to satisfy customer queries, or contains financial information.

 .txtFilter.Visible = True   .txtFilter = strWhere   .lblFilter.Visible = True End With 

To clear the filter we do almost the opposite . We clear the Filter property and set FilterOn to False to indicate that there is no filter in action.

 txtValue = "" With m_rptSales   .Filter = ""   .FilterOn = False 

Since there is no filter in place anymore, we set the indicators to be hidden. So an unfiltered report has nothing extra on it, but a filtered one does.

 txtFilter.Visible = False   lblFilter.Visible = False End With 

That's all there is to it. The steps are really quite simple:

• In the Report's Open event, you open your filter form.

• On the filter form, you construct the filter.

• To apply the filter, you set the Filter property to point to the filter string, and set the FilterOn property to True .

• To clear the filter, you can just turn the FilterOn property to False , set the Filter property to a blank string, or, as I did, do both.

Using this technique, you can construct filter forms quite easily. To construct a generic filter form that could be used to apply to any filter is a little more difficult, since you have to find out the fields on the report, what type of data they hold, and so on. It's something you might want to think about, but it's a little bit too involved to cover here.