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:

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:

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:

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.


Home Index of tips Top