Enter date criteria once for report and subreport

[Fireman1143]

I have a report with a sub-report in the page footer. This is needed because of formatting the page.

I use a form which requires the "start date" and "end date" and then press the preview button to view the report. I am having a problem with the criteria for the sub-report as it requires me to enter the two dates a second time. How would I set the sub-report to use the same criteria as the main report?

Thanks,
Mike

 

[Bry12345]

When you prompt for the beginning and end dates, those two values should be passed to variables. Then format your reports' queries to return rows which occur between those two values.

Also long as all the queries use the same variables (i.e. one variable for beginning date and one variable for end date), you should only have to put in the dates once.

I need to qualify this answer, though. I have done this a number of times with a report containing numerous subreports, however, none of my sub-reports have ever been in a footer. I can't think of why this would make any difference, but nothing would surprise me!

HTH!

 

[Fireman1143]

Thanks for the assist.

I'll try that tomorrow morning.

 

[tekila]

I am facing the same problem but for my case, the main form is based on a select query(SQ1) that is based on crosstab queries and these crosstabs are based on another select query(SQ2), where the starting date and ending date criteria are set. The subform is also based on SQ1, for the sake of formatting report.

Without the subform, the prompts appear once but with the subform, the prompts appear four times. I got what you're trying to say, HTH, but I just could not apply the logic in my report. Pls help.

 

[Bry12345]

I'm guessing that if you input the beginning and end dates each time, the data is returned ok?

I'm also guessing that these are parameter queries in which the query is generating the input boxes? (i.e. The input boxes were not designed by you?)

The problem is that although these queries are running at or about the same time, each of them generates its own set of parameter boxes. Designed in this way, Access has no way of knowing that each set of dialog boxes generated are asking for identical data. The key here is to design a form containing two unbound text controls, which will pass their input to a variable. The queries are then structured so that they return data BETWEEN the two variables to which you passed values in your input form.

What happens then is that each query uses the values from the same set of variables to filter data, and does not generate a parameter box on its own.

See this thread for details on how to do that:

thread703-234319

Hope this helps (HTH)!!!

 

[tekila]

initially, i've done something like what you suggested. now i've decided to create my crosstab queries from the underlying table and set the criteria >=[Forms]![frm]![startdate] AND <=[Forms]![frm]![enddate] in the date fields of all the crosstabs and format the date as &quot;dddd&quot; so the day of the date is returned (cos i'm creating a weekly report with the days of the week as column headings). so the column headings in the crosstabs are Monday to Sunday. by the way, i've ommited the subreport so less problem now.

then i combined all the crosstabs in a select query so that i can have multiple value fields under a column heading in the report. the problem is the fields of the select query have become Monday to Sunday (where the related values of this day are stored) and i'm unable to specify the criteria >=[Forms]![frm]![startdate] AND <=[Forms]![frm]![enddate] anywhere, so there isn't a link between the form {frm) and the weekly report that i want to generate, although i can still view the desired report by entering valid dates in the prompts, on clicking the print preview of the report.

i hope you're not confused. Pls advise.

 

[ScottMusich]

I hope I understand fully your issue.

When you are asking for date (or anything else) - if it is named the exact same way in a report as the query it will display correctly and only ask you once!

Example

In query your criteris is &quot;Between [Start Date] and [End Date]&quot;
Then in the report you can call the dates by doing something like the following (displays date in long format):

=Format([Start Date],&quot;mmm dd&quot;&quot;, &quot;&quot;yyyy&quot & &quot; To &quot; & Format([End Date],&quot;mmm dd&quot;&quot;, &quot;&quot;yyyy&quot.

I hope this helps. Scott Musich
Database Analyst/Programmer