Microsoft Access Tips for Casual Users

Provided by Allen Browne.  Created: 1995.   Last updated: August 2008.

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:

       >= [StartDate] < [EndDate] + 1

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 a command button, and set its Name property to cmdPreview.
4. Set the button's On Click property to [Event Procedure], and click the Build button (...) beside this. Access opens the code window.
5. Between the "Private Sub..." and "End Sub" lines paste in the code below.

   Private Sub cmdPreview_Click()
   'On Error GoTo Err_Handler      'Remove the single quote from start of this line once you have it working.
       'Purpose:       Filter a report to a date range.
       'Documentation: http://allenbrowne.com/casu-08.html
       'Note:          Filter uses "less than the next day" in case the field has a time component.
       Dim strReport As String
       Dim strDateField As String
       Dim strWhere As String
       Dim lngView As Long
       Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Do NOT change it to match your local settings.
       
       'DO set the values in the next 3 lines.
       strReport = "rptSales"      'Put your report name in these quotes.
       strDateField = "[SaleDate]" 'Put your field name in the square brackets in these quotes.
       lngView = acViewPreview     'Use acViewNormal to print instead of preview.
       
       'Build the filter string.
       If IsDate(Me.txtStartDate) Then
           strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
       End If
       If IsDate(Me.txtEndDate) Then
           If strWhere <> vbNullString Then
               strWhere = strWhere & " AND "
           End If
           strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
       End If
       
       'Close the report if already open: otherwise it won't filter properly.
       If CurrentProject.AllReports(strReport).IsLoaded Then
           DoCmd.Close acReport, strReport
       End If
       
       'Open the report.
       'Debug.Print strWhere        'Remove the single quote from the start of this line for debugging purposes.
       DoCmd.OpenReport strReport, lngView, , strWhere
   
   Exit_Handler:
       Exit Sub
   
   Err_Handler:
       If Err.Number <> 2501 Then
           MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
       End If
       Resume Exit_Handler
   End Sub

6. 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.