Prompt for value in report and then display in report 3

[icsupt]

I have a report that I am going to base off of a query. The query will prompt the user for the beginning and end Start Dates:

The field is "StartDate"

So the prompt will ask:
Enter the earliest "Start Date"
Then the second prompt will be
Enter the latest "Start Date"

What I'm trying to do is get all of the Start Dates that happened, let's say, from 8/1/2004 to 8/15/2004.

Then on the report I want to put in the page header: Period 8/1/2004 to 8/15/2004.

Thanks in advance for the help.

This site is oh so wonderful!

 

[THWatson]

icsupt
Here's what I would recommend...

1. Create an unbound form called frmSelectDates. The form will have two controls. Let's call the first one txtStartDate, and the second txtEndDate. These are for passing parameters to the query.
2. In the Date column in the query, put
Between Forms!frmSelectDates!txtStartDate And Forms!frmSelectDates!txtEndDate
3. In the heading for your report put a text box that has something like

="This report covers the period between " & Format([Forms]![frmSelectDates]![txtStartDate],"mmmm dd"", ""yyyy") & " to " & Format([Forms]![SelectDates]![txtEndDate],"mmmm dd"", ""yyyy")

If you want to change the controls to your "select earliest Start Date" and "select latest Start Date", make the necessary changes, but I find that more confusing than Start Date and End Date for the period. However, your call.

Hope this helps.

Tom

 

[THWatson]

icsupt
I should have added that if you don't want to Format the dates in your report heading the way I did, and want to leave them in Short Date style, then remove the Format from the expressions.

Tom

 

[icsupt]

Tom, thanks!

The report is working fine, except that I'm getting the #Name? on my report in the text box.

 

[THWatson]

icsupt
That will be because the report isn't recognizing the controls from the form, frmSelectDates. So, don't run the report from the report itself.

Here's what to do...
On the frmSelectDates, put a Command Button to run the report from there. Set it up, at first at least, to run in Preview mode.

That should do it.

Tom

 

[icsupt]

THWatson - you rock! This works great. Thank you and take a star.

 

[smicro]

Hi THWatson,

This helped me as well, thanks! Is there also a way to add in a customer selection criteria as well? Currently I have the start and end date working like above but I also wanted to add customer. So to allow the user to select a certain customer and date range. Thanks.