Access Report within a Report

[HDFatboy]

I finally got a report my boss likes to see. I have a report within a report. one report uses Microsoft graph to show 11 different items with the cost amount shown. the second report is the written output of the graph. Since these reports are for a given date range it is prompting twice for the date range. So my question is, can I activate both reports with one date range key in?

I am activating the report with the switchboard
Thanks in advance

 

[Remou]

You can put the date in a form and reference the form by adding the name of the form and control to the criteria line of each query, I suppose the reports are based on queries? For example:

[tt]=Forms!frmForm!StartDate[/tt]
Or
[tt]Between Forms!frmForm!StartDate And Forms!frmForm!EndDate[/tt]

 

[HDFatboy]

I will check into this I am a noobie so bear with me...I have a query for the one for but not the other...maybe I need to redo the other report and query thanks

 

[Remou]

Where have you put the parameters that prompt for the date range? You can use the form instead of the prompt.

 

[HDFatboy]

So with the two reports inside of one...Do I point the Record Source to the newly created form on each report?

 

[Remou]

The idea is that you use the form to provide the date parameters, not to provide a record source. What is the current record source for your report? If you post it, it might be easier to explain.

 

[HDFatboy]

The Report that creates the graph uses the main DB table
The report that creates the written info below the graph is from a query I think...lol...Like I said I have been stuggling with access for a bout 3 months now I reall like the way this report looks like...its just a pain to type in date two times...if U skip the first date type in and don't do the second U get the written report and vica verca...

 

[HDFatboy]

I still get the two dates I think it is because I dragged a report into another report. so its still promting me tfor two dates....been at this a long time very frustrated now...I am ready to leave it as a double start and end date report....any further ideas and explanations are welomed...TIA

 

[Remou]

Please post the record source of each report.

 

[HDFatboy]

Thanks for the reply

A form called Reason and Cost for the report with the emmbeded form changed to pivot view to show the chart

I follwed these steps to create it....then I draged in the report called reason and cost

http://office.microsoft.com/en-us/access/HA010346001033.aspx?pid=CL100570041033

Main Query Date Select for the report called reportaddon
that looks like a regular report that shows type of damamge occured, how many times it occured and the total money value for each damage type

I was going to send U a screen shot, but do not see how to attach.

 

[HDFatboy]

Sorry I got messed up in my wording.

A form called Reason and Cost for the report with the emmbeded form changed to pivot view to show the chart
I follwed these steps to create it... report called reason and cost

http://office.microsoft.com/en-us/access/HA010346001033.aspx?pid=CL100570041033

Main Query Date Select for the report called reportaddon....then I draged in the reportaddon in to the open reason and cost report...so there is a chart at the top half and the botton half looks like a regular report that shows type of damamge occured, how many times it occured and the total money value for each damage type

I was going to send U a screen shot, but do not see how to attach.

 

[Remou]

Open the main report in design view and choose View->Properties. Click on the Data tab and you will see Record Source. What does it say?

 

[HDFatboy]

Tool Damage

 

[Remou]

Is Tool Damage a query, a table or a form? I am looking for the place that you are being asked to enter the dates. It is likely that there is a query somewhere with a criteria line that has:

[Enter Date: ]

For example, what does Main Query Date Select look like? What is the SQL?

 

[HDFatboy]

Main Query Date Select is
SELECT [Tool Damage].[Part Number], [Tool Damage].Technician, [Tool Damage].Date, [Tool Damage].Damage, [Tool Damage].[Total Cost], [Tool Damage].[Cost 1], [Tool Damage].[Cost 2], [Tool Damage].[Cost 3], [Tool Damage].[Cost 4], [Tool Damage].[Cost 5]
FROM [Tool Damage]
WHERE ((([Tool Damage].Date) Between [Start Date] And [End Date]));

 

[HDFatboy]

BTW
Tool Damage is a Table

 

[Remou]

Whee! You are using this query in your reports yes no?

SELECT [Tool Damage].[Part Number], [Tool Damage].Technician, [Tool Damage].Date, [Tool Damage].Damage, [Tool Damage].[Total Cost], [Tool Damage].[Cost 1], [Tool Damage].[Cost 2], [Tool Damage].[Cost 3], [Tool Damage].[Cost 4], [Tool Damage].[Cost 5]
FROM [Tool Damage]
WHERE [Tool Damage].Date) [blue]Between Forms!frmForm!StartDate And Forms!frmForm!EndDate[/blue]

This is where the form we were talking about comes in.

 

[HDFatboy]

yes I am using it one out of two reports...

 

[HDFatboy]

Thanks Remou for the help. So I changed the sql to what you changed it to, and now I amm getting sytax errors. I really do not know what I am doing wrong with this. Thanks again for trying to help me.

 

[Remou]

Always post the error(s) you are getting. It often helps to post back SQL and code that you have modified.

Have you got the form you created for the dates open? Have you changed the names to match the names on your form?