Allen Brownes Limiting a Report to a Date Range tip

[hect1c]

Hi

I followed Allen Brownes code in this Ms Access tip
http://allenbrowne.com/casu-08.html

However I keep getting an error, not sure what I need to do to fix this error. The line the error refers to is in bold below. Thank you for you help.

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 = "withDateCrossTabQueryUsingUnion" 'Put your report name in these quotes.
strDateField = "[ActivityDate]" '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

[burrina]

I have not tried that code but personally, I always use either a popup date range form or have the report itself ask for it.

[hect1c]

A pop up date range form! Have you got an example?

[burrina]

Sure. I generally use my Main form with start and end dates. See this Example: http://ms-access-tips.blogspot.com/2...ogue-form.html

[orange]

Further to burrina's suggestions, please see Martin Green's site and example.

[hect1c]

Thank you all for your assitance, much appreciated.

[June7]

What is the exact error message?

[hect1c]

After reading through the tips I received, I went through the code and found a variable not declared and an extra comma. The dates and the function work well from the form but when I run the report from inside the report design the dates do not work and show an error message. This additional problem is not really a concern because I would be running the reports from the form not through the report design. It could be the way I have set it up.

[June7]

Something in the report is dependent on the form. If the form is not open the report has issues.

This is resolved?

[hect1c]

Yes it is thank you!