Microsoft Access Tips for Casual Users

Provided by Allen Browne, allen@allenbrowne.com

Limiting a Report to a Date Range

Here are two methods to limit the records in a report to a user-specified range of dates.

For a more comprehensive example that combines other criteria with the dates, see Search Criteria database.

Method 1: Parameter query

The simplest approach is to base the report on a parameter query. This approach works for all kinds of queries, but has these disadvantages:

• Inflexible: both dates must be entered.
• Inferior interface: two separate dialog boxes pop up
• No way to supply defaults.
• No way to validate the dates.

To create the parameter query:

1. Create a query to use as the RecordSource of your report.
2. In query design view, in the Criteria row under your date field, enter:

       Between [StartDate] And [EndDate]

3. Choose Parameters from the Query menu, and declare two parameters of type Date/Time:

       StartDate    Date/Time
       EndDate      Date/Time

4. To display the limiting dates on the report, open your report in Design View, and add two text boxes to the Report Header section. Set their ControlSource property to =StartDate and =EndDate respectively.

(Note: Step 3 is optional, but strongly recommended. It prevents invalid dates being entered, and helps Access understand the date regardless of your regional setting date format.)

Method 2: Form for entering the dates

The alternative is to use a small unbound form where the user can enter the limiting dates. This approach may not work if the query aggregates data, but has these advantages:

• Flexible: user does not have to limit report to from and to dates.
• Better interface: allows defaults and other mechanisms for choosing dates.
• Validation: can verify the date entries.

Here are the steps. This example assumes a report named rptSales, limited by values in the SaleDate field.

1. Create a new form that is not bound to any query or table. Save with the name frmWhatDates.
2. Add two text boxes, and name them txtStartDate and txtEndDate. Set their Format property to Short Date, so only date entries will be accepted.
3. Add two command buttons for Ok and Cancel. Set the Ok button's Default property to Yes, and the Cancel button's Cancel property set to Yes.
4. Set the On Click property for both buttons to [Event Procedure]. Beside this is a "..." button which opens the code window to the procedure.
5. Enter this line into Cancel button's Click procedure (between the "Private Sub ..." and "End Sub" lines):
       DoCmd.Close acForm, Me.Name
6. Paste this into the Ok button's Click procedure:

       Dim strReport As String     'Name of report to open.
       Dim strField As String      'Name of your date field.
       Dim strWhere As String      'Where condition for OpenReport.
       Const conDateFormat = "\#mm\/dd\/yyyy\#"
   
       strReport = "rptSales"
       strField = "SaleDate"
   
       If IsNull(Me.txtStartDate) Then
           If Not IsNull(Me.txtEndDate) Then   'End date, but no start.
               strWhere = strField & " <= " & Format(Me.txtEndDate, conDateFormat)
           End If
       Else
           If IsNull(Me.txtEndDate) Then       'Start date, but no End.
               strWhere = strField & " >= " & Format(Me.txtStartDate, conDateFormat)
           Else                                'Both start and end dates.
               strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
                   & " And " & Format(Me.txtEndDate, conDateFormat)
           End If
       End If
   
       ' Debug.Print strWhere                  'For debugging purposes only.
       DoCmd.OpenReport strReport, acViewPreview, , strWhere

7. Open the report in Design View, and add two text boxes to the report header for displaying the date range. Set the ControlSource for these text boxes to:
      =Forms.frmWhatDates.txtStartDate
      =Forms.frmWhatDates.txtEndDate

Now when you click the Ok button, the filtering works like this:

• both start and end dates found: filtered between those dates;
• only a start date found: records from that date onwards;
• only an end date found: records up to that date only;
• neither start nor end date found: all records included.

You will end up using this form for all sorts of reports. You may add an option group or list box that selects which report you want printed, and a check box that determines whether the report should be opened in preview mode.