Provided by Allen Browne. Created: 1995. Last updated: August 2008.
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.
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:
>= [StartDate] < [EndDate] + 1
StartDate Date/Time EndDate Date/Time
(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.)
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.
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
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|