creating Formt o Enter Start & End Dates to View Report

[dcrqueens]

I have a form that I am trying to get to work with no success. Basicaly I have a query that a report is based off of. Instead of using the general prompt that comes up to run the query I want to create a form to enter the start and end date. I created the form using the instructions from http://allenbrowne.com/casu-08.html There are two boxes on the form txtStartDate and txtEndDate the form name is fromWhatDates. The query is called QryReviewStartEndDate and in the criteria for”Review Start Date” in my query I have >= [StartDate] < [EndDate] + 1. The report name is Rpt ReviewStartEndDate. In the header of the report I have two text boxes with the control source sent to =Forms.frmWhatDates.txtStartDate and =Forms.frmWhatDates.txtEndDate Once I enter the dates I have a botton to run the query with the following code in the On click event:

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 = "Rpt ReviewStartEndDate" 'Put your report name in these quotes.
strDateField = "[Review Start Date]" '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

After I enter the start and end date, then click the button to run the query I get the “Enter parameter Value” prompt for StartDate and EndDate.
Does anyone have any ideas on how I can correct the problem?

[pbaldy]

To use that technique, you'd take the criteria out of the query. The code is doing the filtering.

[dcrqueens]

Hi phbaldy thank you for replying. I have tried to remove the code as well and it still gives me the pop ups to enter start and end dates.

[pbaldy]

Does the report open without prompts if you open it directly? If so, something is likely spelled wrong in the code. Can you post the db here?

[dcrqueens]

It is fixed thank you. Your question regarding the promps got me thinking. So I made a copy of the query that the report is using and gave it different name. Now it works. I dont know how that is bit it is fixed!!

[rpeare]

pbaldy's already solved your problem but I'm curious why you wouldn't just use criteria in your query rather than filtering

i.e. in the criteria of your query put


between [forms]![fromWhatDates]![txtStartDate and [forms]![fromWhatDates]![txtEndDate]

Then all you have to do on your buttonclick is check to see if the start and end date are entered and run the report if they are.

[pbaldy]

Happy to help and welcome to the site!