Reports (A2K)

Topic

New Reply

Greetings: I have a report, rpt_Combined_Report, that consists of 3 subreports. Each subreport calculates a total number of hours. The subreports are each based on a separate query. What I would like to do is calculate a grand total of hours, using each subreports calculation. The names of the subreports and total controls are as follows:

rpt_EMT_Refresher![Total Time]
rpt_CME_Totals![Sum of Hours]
rpt_Run_Review![Sum of NewTime]

I tried this: =rpt_Combined_Report!rpt_Emt_Refresher![total time]+rpt_Combined_Report!rpt_CME_Totals![sum of hours]+rpt_Combined_Report!rpt_Run_Review![sum of newtime]

The result that I got was #Name?

Any and all assistance is greatly appreciated. TIA.

Reply | Quote

Replies

Check carefully that the names you use are those of the subreports as controls on the main report. They are not necessarily the same as those of the subreports in the database window.
Open the main report in design view, and click once on one of the subreports. You’ll see the control name in the caption of the Properties window, and of course in the Name property.
Also check carefully that you are using the correct names for the controls on the subreports displaying the total time.

Reply | Quote

What happens when you change the ! to a . after the name of the main report name (the main report name I assume is rpt_Combined_Report)? Something like:

=rpt_Combined_Report.rpt_Emt_Refresher![total time]+rpt_Combined_Report.rpt_CME_Totals![sum of hours]+rpt_Combined_Report.rpt_Run_Review![sum of newtime]

Reply | Quote

Pat and Hans: Thanks for your quick replies. I did as both suggested but to no avail. The names were all correct and whether I use the ! or the . made no difference. I will keep plugging away at this. The controls, [sum of hours], [sum of newtime] and [total time] are calculated controls on the subreports. This may be the reason I am experiencing difficulty. Thanks again.

Reply | Quote

Could you post a stripped down copy of your database? See post 401925 for instructions.

Reply | Quote

Hans: Here it is. Use frm_CME_Report to initiate the report. I have been using b027, a003 as the IDs. Thanks again.

Reply | Quote

The control source of the Grand Totals text box should not mention the main report itself. Moreover, total_time is on the rpt_Emt_Refresher subreport of the rpt_CME_Totals subreport. So the control source should be:

=rpt_CME_Totals!rpt_Emt_Refresher![total time]+rpt_CME_Totals![sum of hours]+rpt_Run_Review![sum of newtime]

Reply | Quote

Hans: Thanks again for the tips and the solution. Works fine.

Reply | Quote

(Edited by Francois on 09-Sep-05 17:46. Remove a 0 that was to much)

You can use the IsError function:

=IIF(IsError(rpt_CME_Totals!rpt_Emt_Refresher![total time]),0,rpt_CME_Totals!rpt_Emt_Refresher![total time])+IIF(IsError(rpt_CME_Totals![sum of hours],0,rpt_CME_Totals![sum of hours])+IIF(IsError(rpt_Run_Review![sum of newtime],0,rpt_Run_Review![sum of newtime])

Reply | Quote

Francois & Hans:

Thanks for both solutions. Both worked. I can enjoy my weekend and hope you both do the same. Thanks again.

Reply | Quote

One thing I overlooked and it just reared its ugly head, is how to handle the calculation if a subreport has no data. If you could assist me with this, I would be most appreciative. TIA.

Reply | Quote

Another possibility is to use the HasData property of the subreports:

=IIf(rpt_CME_Totals!rpt_Emt_Refresher.Report.HasData,rpt_CME_Totals!rpt_Emt_Refresher![total time],0)+IIf(rpt_CME_Totals.Report.HasData,rpt_CME_Totals![sum of hours],0)+IIf(rpt_Run_Review.Report.HasData,rpt_Run_Review![sum of newtime],0)

Reply | Quote