SubReport Total Displays "#Error"

[wonderwhy]

Hello,

I have been working on this same report all day. Luckily, I have found answers to most of my questions thanks to TekTips! But...

I am still having difficulty with a subreport. I have subreport that is linked to the main report (by master/child fields and in the relationships) The report has two grouping levels - the first is by "Year" (the DateApproved field from the main table/report formatted as yyyy in the underlying query). The 2nd grouping level is by "ProtocolNumber" which is the linking field between the two tables/reports.

The subreport is located in the detail section the main report.

In the "Year" footer I have a =Sum([AwardAmount])of a main form currency field which works fine. Also in the "Year" footer I have a reference to =Sum([ExpenseAmount])field which is in the subform report footer, but not visible (=([rptSubBRFDisbursements].[Report]![TotalExpenseAmount]). This reference works for Year groups up until 2002 and 2003. For 2002 and 2003 the field displays "#Error".

The only thing that I can think of is that 2002 and 2003 have records (in the Main Report) that do not have records in the subReport. This doesn't cause any problems in the detail section where the subReport records either show up or don't. But, when I add the referenced field into the "Year" footer I get an error.

I'm not sure what to do. I know this is confusing. Does anyone have any ideas?

Thank you

 

[wonderwhy]

Sorry, I inadvertently called the report and subreport a form and subform... in my above description of the problem. I am only dealing with a Report and SubReport.

 

[wonderwhy]

Hello,

Does anyone have any helpful hints to fix my error message?

Thanks!

 

[dhookom]

You can use
=IIf([rptSubBRFDisbursements].[Report].[HasData],[rptSubBRFDisbursements].[Report]![TotalExpenseAmount],0)

Duane
MS Access MVP

 

[wonderwhy]

Thank you, Duane. I have been out of the office for a couple of days. I will try this tomorrow and post back.

Thanks again,

Julia

 

[wonderwhy]

Well, I have finally had a chance to try to implement the solution that Duane provided. After some thought I decided to modify the expression given above and use it in the underlying query for the subreport. Like this:

ExpenseAmt: IIf(IsNull([ExpenseAmount]),0,[ExpenseAmount])

This helped by displaying a zero instead of just nothing when there are no subreport records. Now, it seems like it would be really simple to refer to the field [ExpenseAmt]in the footer for the "Year" group on the main report and have it total that field by year, but I haven't had any luck. Now, instead of an "Error#", I am getting a zero in that field in the footer (just for 2002 and 2003, the other years work).

Once again, 2002 and 2003 are the only years where SOME of the main report records have no subreport records (some do, some don't). All other years have an accurate total in this footer. I guess it is just returning a zero for any year that has some zero value subreport records. I have tried:

=([rptSubBRFDisbursements].[Report]![TotalExpenseAmt])
and
=Sum([rptSubBRFDisbursements].[Report]![TotalExpenseAmt])

the later gives me a Enter Parameter Value dialog box.

Please help. What am I doing wrong?

Thank you!

Julia

 

[dhookom]

If there are no records returned in a subreport, then you can't reference any controls on the subreport.

Did you attempt my suggestion?

Duane
MS Access MVP

 

[wonderwhy]

Duane,

I did use your suggestion. This worked for each Year footer subtotal except fot 2002 and 2003. For those years nothing shows up at all (not an error or a zero).

Thank you for trying. Is there anything else that I could tell you to help? Any other suggestions?

Julia

 

[dhookom]

Did you place the expression in a control on the main report? What expression, exactly did you try?

Duane
MS Access MVP

 

[wonderwhy]

I used:

=IIf([rptSubBRFDisbursements].[Report].[HasData],[rptSubBRFDisbursements].[Report]![TotalExpenseAmt],0)

TotalExpenseAmt is the name of the control on the subform which is in the report footer of the subform. It is derived from the expression:

=Sum([ExpenseAmount])

in the YearofApproval Footer. This is a textbox. This is the footer where another subtotal (from the main report) is located. The total from the main report is a total of all award amounts (stored in the main table). I am trying to get a total, from the subreport, of all disbursements from the original award (which are stored in a separate table). I have the report grouped by the YearofApproval (from the main report) and then by Protocol (from the main report).

The disbursements are showing up with no problem under each Protocol (when I had the HasData expression in the underlying query for the subreport I got a zero in in the subreport under each Protocol that had no disbursements).

Thank you again, Duane, for your time.

Julia

 

[wonderwhy]

sorry, i refered to a subreport as a subform, again....

 

[dhookom]

I'm not sure what you meant by "when I had the HasData expression in the underlying query for the subreport". The HasData expression is supposed to be used in a control source property of a text box in the group footer of the main report.

Duane
MS Access MVP

 

[mbuell]

Wonderwhy: did you ever resolve this? How?

If you are still getting an error on the main report, I'm wondering if the summation on the subreport is giving you an error. Can you pull the same summation values up in a query? If no, then maybe you have some record problems.

Second: if you are doing the same subtotal technique in the Year footer and the Protocol footer, but only getting an error for Year, then look at the Year records. There may be some funky records in there.



Mark
<O>
_|_