Filtering Report Results Using the Filter Property

In this tip we are going to filter the results of a Report to display records with order amounts above a given value.  In so doing we are going to pay special attention to two Report properties: these are FILTER, and FILTER ON LOAD.

Our solution works when the user opens a Dialog Form containing a text box and command button.  The user enters an amount value in the text box, and clicks the command button.  The command button then runs a small amount of VBA code to open the report.  As the report loads, it applies a filter defined in the Report's Filter Property to produce the desired result.

Figure 1: Dialog Form to collect the Amount Parameter.

So how does this work?

When the Report opens, Access checks the report's FILTER ON LOAD property (which we have set to YES). When it detects the property is set to YES, this tells Access to apply the report's Filter (which we have entered into the report's FILTER property). Our filter then references the users parameter value from the Dialog Form text box, and filters all records with Order Amounts greater than or equal to that value.

You can download the completed solution from here: Filter Property Tip Database. You may also want to work your way through the instructions below.

The Table

The solution contains a Table (tblOrders), a Dialog Form (frmParameter) and a Report (rptOrders).  In the screenshot, below you will see the data we shall be working with in tblOrders:

Figure 2: tblOrders.

As you can see the table is made up of four fields: OrderId (AutoNumber), OrderDate (Date/Time), Customer (Text), and Total (Currency).  

The Dialog Form

We then create the dialog form shown in Figure 1 above (there is more information about Dialog Forms in my last post).  I have referred to the textbox as txtAmount, the control button as cmdOpenReport, and the form itself as frmParameter.

As I have already mentioned, the command button runs a small amount of VBA Code when clicked by the user.  To add this code:

1. Select the command button on the form design grid, and click the PROPERTY SHEET icon (located in the TOOLS group of the DESIGN ribbon).  
2. When the PROPERTY SHEET opens, click the EVENT TAB.  
3. Then select the ON CLICK event by clicking into that particular cell of the grid.  
4. A three dot  symbol appears at the right edge of the cell. Click it and select CODE BUILDER from the CHOOSE BUILDER dialog box which has opened.  
5. Then click the OK button and the VBA editor opens.

Figure 3: The On Click Event on
the PROPERTIES SHEET.

You can can copy the code below and paste it onto the editor.  The top and bottom lines that begin Private Sub... and End Sub respectively should have appeared already, so you just need to copy the middle section in between:

---

Private Sub cmdOpenReport_Click()

On Error GoTo MyError

    DoCmd.OpenReport "rptOrdersFiltered", acViewReport

    DoCmd.Close acForm, Me.Name

Leave:

    Exit Sub

    

MyError:

    MsgBox "Error " &  Err.Number &  ": " &  Error$

    Resume Leave

End Sub

---

The two important lines in this section of code begin with DoCmd (meaning do command).  The first of these is the command to open the form in Report View.  The second closes the Dialog form once the report has opened.  NB it is very important the report opens before  the Dialog Form closes, because it needs to reference the value contained in the Dialog Form's Textbox as the it loads.  

The Report

The quickest way to create the Report is to base it on our table.

1. Select tblOrders in the Navigation Pane.  It will highlight in orange.
2. Then select  the CREATE ribbon , and click the REPORT icon (located in the REPORTS GROUP). 

This creates a basic report which we can then then modify in DESIGN VIEW as required. 

Figure 4: Report Design.

Setting the Filter Properties

Next we turn our attention to setting the Report's Filter Properties accessed via its Property sheet.

1. Click the Square Box in the top right hand corner of the Report Design Grid.  This ensures we are going to be working the properties of the actual Form (rather than one of its controls).
2. Click on the PROPERTY SHEET icon (located in the TOOLS group of the DESIGN ribbon) whilst the Report is open in Design View.
3. Click the DATA TAB of the pane.
4. Make sure the SELECTION TYPE is set to REPORT.  This should be the case if you carried out step 1 successfully.  Otherwise just change the selection to Form in the drop down list.
5. Enter the following into the FILTER property cell: Total >= forms![frmParameter]![txtAmount] .  This has the effect of filtering out report records where the amount value in the Total field is greater or equal to that entered by the user in the Dialog Form's textbox.
6. Then set the FILTER ON LOAD PROPERTY to YES.  This ensures the filter is applied when the forms loads.
7. Save the Report as rptOrdersFiltered.
8. Close the Report

Figure 5: The Form's Filter Properties.  Note the reference to the textbox
on our Dialog Form in the FILTER property cell.

The solution should now be ready to run.  Just click the frmParameter form in the Navigation Pane to begin.  The screenshot below shows what the report results look like when £50,000 is entered by the user as the amount parameter: 

Figure 6: Report Results filtered for amounts greater than or equal to £50,000.

Creating a Dialog Form

Dialog Forms are an integral part of most customized Access Database Applications.  As developers, we set them up to enable our applications to communicate and interact with users.  The simplest sort of dialog box is one which displays a message.  These may be created using VBA's MsgBox Function or the MsgBox Macro Action.   Others involve a greater degree of customization, especially when the application requires the user to provide information such as parameters for a query.  One way to do is to create a standard Access form and modify it's properties to make it into a Dialog Form.

Figure 1: This Dialog Form obtains date parameters used to restrict the
records displayed in a report.  You may remember this was NorAzri's chosen
method for filtering report results mentioned in my last post.

To create a Dialog Form such as that displayed in figure one, we begin by creating an unbound form.  This is done by clicking the FORM DESIGN icon from the FORMS group of the CREATE ribbon.  The term unbound means that the form is not bound to a particular Table as with conventional forms.  The information it is designed to collect is temporary, and does not need to be stored.

When a new form opens in design view, the screen will show a blank grid in the main window, and a variety of  form controls in the CONTROLS group of the DESIGN ribbon.  The Dialog Form in figure 1 uses four Combo Box Controls and a Command Button.  The values displayed in the Combo Boxes are defined in the Row Source Property.  I recently blogged about Customizing an Access Combo Box.  If you are interested, I recommend you check it out by following the link above (just remember that the combo boxes in this example do not need a Control Source because we do not need to store it's data in a table).

However in this post we are mainly interested in the process of changing the appearance and action of an ordinary form to that of a Dialog Form.  So how is this done?  The answer to this lies in a number of key form properties.  The two most important properties to understand are the POP UP and MODAL properties.

The POP UP property ensures that when the dialog form is opened, it appears as a pop up box rather than a full size form. Otherwise it would just appear as an ordinary form rather than dialog form.  As such this property should always be set to YES.  Setting the MODAL property to YES ensures the user cannot ignore the dialog form by clicking on a ribbon icon or form control, for example.  The idea here is that your application is waiting for a user response. Access will not do anything until that response is given, and/or the dialog form is closed.  These properties are located on the OTHER TAB of the PROPERTY SHEET.

Figure 2: Set the POP UP and MODAL properties to YES.  This is done
 from the OTHER TAB of the PROPERTY SHEET.  The property
sheet is accessed by clicking the PROPERTY SHEET icon
on the TOOLS group of the DESIGN ribbon.

NB When you change these properties make sure the SELECTION TYPE of the PROPERTY SHEET is set to FORM.  If it is not, just click the drop down list at the top of the sheet and change it. Alternatively click the square box at the top left hand corner of the DESIGN GRID. 

If you look again at the dialog form in figure1 you will notice there are no navigation buttons or record selectors which forms normally have by default.  To remove these just go to the FORMAT TAB of the PROPERTY SHEET and change the NAVIGATION BUTTONS and RECORD SELECTORS properties to NO.

You may also notice that our dialog form has a border style specific to dialog forms.  One of its features is that the user cannot resize it, thereby diminishing its impact!  This is attained by changing the BORDER STYLE property to DIALOG.

One of the more subtle properties that you might want to change is that of the SCROLL BARS property.  I recommend you set this to NEITHER.  Apart from the fact you should not need to scroll a dialog form, Access also leaves a thick white line at the bottom of the form if this is not changed.  This looks particularly bad if you choose a different Back Color for your form.

One last property alteration that I recommend, is the addition of a form Caption.  This appears in the top border of the dialog form.  In figure 1 I have set this to "Please Enter a Date Range".  The caption does not have to be a question, generally it is just a form title.  Either way you will need to choose your own caption and enter it in the CAPTION property, the first property on the FORMAT TAB.

As far as the actual function of the dialog form is concerned, this particular example runs a VBA sub when the command button is clicked.  The code creates an SQL WHERE statement based on information obtained from data entered by the user on the dialog form.  This is part of the code that I used:


varYearFrom = Me!cboYearFrom
varYearTo = Me!cboYearTo
varMonthFrom = Me!cboMonthFrom
varMonthTo = Me!cboMonthTo

strSQL = "(Year between " & varYearFrom & " AND " & varYearTo & ") AND (Month between " & varMonthFrom & " AND " & varMonthTo & ")"


As you can see, each combo box value is stored in a variable in the first four lines of code.  These variables are then integrated into the string containing the SQL Statement. Once we have the SQL Statement stored in the string strSQL, this can be passed as the WHERE parameter of the DoCmd.OpenReport Method like this:


DoCmd.OpenReport varForm, acViewReport, , strSQL


Then, when our report opens, only records falling within the criteria set in the SQL WHERE statement are displayed. In this case these are all the records between January and December 2011 (see figure 1 above).

Manipulating Dates Using the DatePart Function

I was recently asked a question on my Access 2007 Tutorial Facebook Page which involved the manipulation of dates.  In my answer to this question, I suggested a solution that made use of the DatePart Function.  If you are interested, I was replying to NorAzri's comment on his post of the 5th Sep 2011.  However, manipulating dates in this way is quite an interesting area, so I thought I would use this blog to write a bit more about the function.

So what does DatePart actually do?  Put simply, the DatePart Function allows us to isolate part of a given date.  In so doing we may then go on to group or retrieve records according to the date interval set . We use the function by passing an interval parameter telling Access which part of the date we are interested in, and a second parameter which is the date itself.  The syntax for the function is constructed as follows:

DatePart("Interval", DateValue)

The interval parameter is comprised of the same values as that used in the related DateDiff Function - a function I blogged about in August.  These intervals may pertain to the Year ("yyyy"), the Quarter ("q"), or the month ("m") to name but three. There are ten possible interval types in total (going right down to hours, minutes, and seconds).

However, NorAzri was interested in filtering his records by month.  So in order to do this we passed "m" for the interval parameter, and fldDate (a field value from the table) for the date parameter.  The syntax for this was written as follows:

DatePart("m", [fldDate])

The result of the function gave us a numeric month value based on the full date.  For example, if the date in fldDate had been 16/09/2011, the function would have returned the value 9. This is useful when we have a list of dates, such as that in Figure 1 below, which we want to group by month value in a query or report.

Figure 1:  List of Orders with dates.

So lets take a look at how we would use the DatePart Function in a query to group these order records by month:

Figure 2

As you can see in Figure 2 above, we have created a calculated field called TheMonth using the DatePart Function.  The interval, "m", has been passed in the first parameter, and the second parameter references the OrderDate field from tblOrders.  We have also clicked the TOTALS icon from the SHOW/HIDE group of the DESIGN ribbon, setting TheMonth and Total fields to GROUP BY and SUM respectively.

When we run the Query, any month containing an order will be represented in the results as its own individual row.  The first column then displays the Month value derived from the DatePart function in TheMonth, and the second contains the sum of order totals for the particular month in question.  Figure 3 below shows the query result for our sample data:

Figure 3: Results of Query with Calculated
Field using the DatePart Function.  SumOfTotal
gives us the total for each group of order records
falling within each Month.

Another excellent use of DatePart would be to filter a group of records using a criteria based on this function.  For example, if we wanted to show each individual record for all orders falling within the month of May, we would construct our Query as follows:

Figure 4

As you can see, we have kept our calculated field called TheMonth.  However, instead of grouping all the records from tblOrders by month, we have added the month value =5 as a criteria for this column.  When we run the query, all Orders from May will be displayed.

Figure 5: The result of the second Query filters
out all orders from May.

If we wanted, we could convert this to a parameter query, and use it as the data source for a report.  This was something I recommended to NorAzri who wanted to select the Month from a Combo Box on an unbound form, and then produce a report which would filter out records falling within the month in question.  If you are interested in seeing the solution I suggested, just follow the link at the top of this post and look down the stream.

How to Display a Form Automatically when your Application Opens

This is a quick tip on how to automatically display a form when the user opens your Access Application.  In addition to improving User Friendliness, your database design will also appear much more professional.  The step by step instructions below will display the Switchboard form in figure 1 immediately upon the application opening.  This will give them a good start point from which to navigate your system.

Figure 1: Switchboard Form opens Automatically
when the Application Opens.

1. Open the Database and create a Switchboard Form similar to that in figure 1 above.  Call the form frmSwitchboard.
2. Click the MICROSOFT OFFICE button in the top right hand corner of the Access Screen.  
   
3. Click the ACCESS OPTIONS button at the bottom of the open pane.  This opens the ACCESS  OPTIONS dialogue form.
4. Select CURRENT DATABASE from the menu on the left side of the dialogue form.  This displays the Options for the Current Database (see figure 2 below).

   

   Figure 2: Options for the Current Database.

5. The fourth option down from the top is DISPLAY FORM.  Click the Combo Box arrow and select frmSwitchboard from its drop down list, 
6. Click OK to close the ACCESS OPTIONS form.
7. Close the database.

The next time you open your application, the Swichboard is displayed automatically without the user needing to select it from the Navigation Pane.

Removing Multiple Records with a Delete Query

Imagine you have a contact database containing a list of suppliers.  One of those suppliers proved themselves unreliable once to often, so your company decides not to use them any more.  It is your job to delete any names from this list that belong to this particular supplier. The only problem is, the contact database contains hundreds of names, a number of which are from the suppliers company. This is where an Access Delete Query is going to prove very useful.

Delete Queries are a type of Action Query ie a Query which performs some sort of action on our database.  In the case of a Delete Query, Access will find a group of records matching a given criteria and delete them from the database table.  As such, we can make good use of a Delete Query in the scenario outlined above.  By means of a Delete Query, we can find all the records of staff who work for the ex-supplier and delete them from the contact list.  Lets take a look at the contact list we are going to working with (shortened for the purpose of this exercise):

Figure 1: The Contact List upon which
we shall run our Delete Query.

Lets say the name of our ex-supplier is Company 5.

So how do we create a Delete Query?

It's quite simple really.  The first stage is to create an initial select query that would give us a list of all the names of staff working for Company 5.  This involves entering "Company 5" as the query criteria. Once we have run the query and checked its results, we just need to change it to a Delete Query and click RUN again.

How to Create a Delete Query

Stage 1 - Creating the initial Select Query

1. Select the CREATE TAB of the Access Ribbon.
2. Click the QUERY DESIGN icon.  It is located in the OTHER group of the CREATE ribbon.
3. Select tblContacts from the SHOW TABLE dialogue box.
4. Drag the asterix (*) from tblContacts down to the first column of the DESIGN GRID.  This is a way of getting the query results to display all fields from the table without having to select each one individually.
5. Then Drag the Company field from tblContacts down to the second column of the grid.  We have added this field separately because we are going to enter a criteria in this column.
6. Click on the CRITERIA row of the Company column, and add the criteria: "Company 5"

The Select Query has now been created.  It should look like this:

Figure 2: The Select Query created in the
first stage of the Delete Query.

It is advisable to run the query at this point and check the results are correct.  They should look like this:

Figure 2: The results from the Select Query.

As you can see, our select query has found four records from tblContacts matching the criteria of Company 5".  Since this is the correct result for the dataset we are working with,  we can move onto the second stage of the process: converting the Select Query to a Delete Query.

Stage 2 - Converting the Initial Select Query to a Delete Query

1. If you look at the QUERY TYPE group of the DESIGN ribbon, you will notice that the SELECT QUERY icon is highlighted orange.  We need to change this to DELETE QUERY.  To do this just click the DELETE QUERY icon further along the group.  

   

   Figure 3: The QUERY TYPE group of the DESIGN ribbon.
   The DELETE QUERY icon is highlighted orange.
2. After the clicking the DELETE icon, you will notice that the row of SHOW tick boxes disappears from the DESIGN GRID, along with the row for SORT. A new row entitled DELETE has taken their place.  Access has filled in the values of FROM and WHERE in the first and second columns respectively.  These are SQL Keywords: the FROM keyword indicates the first column contains fields from tblContacts, and WHERE indicates the Company column contains a criteria against the data stored in this field. 

   

   Figure 4: The QUERY DESIGN GRID for our
   DELETE Query.  Notice the new row for DELETE
   containing the SQL FROM and WHERE
   Keywords.

3. Click RUN from the QUERY RESULTS group.  
4. Click YES when prompted whether we want to delete the number of rows matching our query criteria.  This will be four rows for the dataset we have been working with.

We can now go back and open the tblContacts table.  As you can see from Figure 5 below, all Company 5 contacts have been removed by our DELETE QUERY.

Figure 5: The tblContacts table after Company 5
contacts have been removed.