I have a report with a subreport embedded on the Main report. I would like to allow the users to filter the subreport records on a date field in the subreport . Is that possible? How could that be accomplished?

The Main report is named [ProjectDetailsSaveEstimate]
The Subreport is named [SaveDetailReport]
In the Subreport is a field named [SaveDate]

I want to allow the user to create a date range for [SaveDate] displaying only those records in each Subreport [SaveDetailReport] that fall into the selected date range.

I tried to create a query for the subreport but when I ran the Main Report it kept prompting multiple times for the start and end date of the range and did not work.

Thanks for any assistance.

i'm guessing that you built the query with prompts as the criteria ([Enter start date], [Enter end date], for instance), so inputbox prompts came up when you ran the report, asking for the date values to be entered. if that's how you did it, then suggest you try the following instead:

1. create a form, and add two unbound textbox controls, txtStartDate and txtEndDate. also add a command button to open the main report.

2. write your query using form control references as the criteria for the date field, as

WHERE SaveDate Between Forms!MyFormName!txtStartDate And Forms!MyFormName!txtEndDate

you may also need to set query parameters to declare Forms!MyFormName!txtStartDate and Forms!MyFormName!txtEndDate as Date/Time data types. make sure that you replace MyFormName with the correct name of the form.

3. enter the start and end dates in the form controls, then click the command button to run the main report. make sure you keep the form open.

hth
tina

I removed the user prompts from the design of the query on the subreport and replaced it with an expression show below.
I created an input form with text boxes to capture the user input and set the parameters to Date/time for the text boxes [SaveStart] and [SaveEnd]
Input Form = [ReportCriteriaSaveDetail]
I created a command button on the input form to run the Main report
Now, similar to the original query, I get multiple dialog boxes asking me to "Enter Paramenter Value";
I am asked multiple times for the value of "[Forms]![ReportCriteriaSaveDetail]![SaveStart]" and for "[Forms]![ReportCriteriaSaveDetail]![SaveEnd]

Here is the SQL statement created for the query for the subreport:
PARAMETERS [Forms]![ReportCriteriaSaveDetail]![SaveStart] DateTime, [Forms]![ReportCriteriaSaveDetail]![SaveEnd] DateTime;
SELECT tblSaveDetail.SaveID, tblSaveDetail.ID, tblSaveDetail.SaveAmount, tblSaveDetail.SaveComment, tblSaveDetail.SaveDate
FROM tblSaveDetail
WHERE (((tblSaveDetail.SaveDate) Between [Forms]![ReportCriteriaSaveDetail]![SaveStart] And [Forms]![ReportCriteriaSaveDetail]![SaveEnd]));

When I run the subreport by itself with the above query everything works perfectly. It is when I run the Main report with the subreport embedded that I get the multiple "Enter Parameter Value" problem.

In theory, is there a problem running the query on the table that creates the subreport and then expecting that to work as an embedded object in the Main Report?

I can't seem to find the error that is causing the problem. I have checked and double checked and can't seem to solve this.
Thanks for your assistance.
Tom

Hi Tom,

The only thing I can think of is if your FormName is correct in your FormReference:

[Forms]![ReportCriteriaSaveDetail]![SaveStart]
[Forms]![ReportCriteriaSaveDetail]![SaveEnd]

Is ReportCriteriaSaveDetail the actual name of your Form? Please doublecheck.

Hope this helps,

Daniel

Just to confirm, does the form - ReportCriteriaSaveDetail - remain open when you execute the main report? It must be open for the sub-reports source query to see it.

Thanks to everyone for helping me on this. The problem is solved. I had the selection form set as a Pop Up form. So when the report ran, the Pop Up form stayed on top of all and obscured the view of the report. I thought that since the report had already run, it would be okay to close the Pop Up form, but that was a bad assumption. I changed the settings on the input form to a standard "non-Pop up" form, reran the report letting the input form stay open but now it goes to the background. So now while reviewing and printing the report everything works.

When all else fails, follow the instructions! Thanks again for all the help.

Tom