Referencing fields on a subreport

[jgarnick]

I have a report with a subreport. I need to sum the data on the subreport and put the total on the report. Is there a way to reference the fields on the subreport in an expression? I haven't been successful using the expression builder.

Thanks-

jgarnick
jgarnick@aol.com

 

[DougP]

Here is a trick I picked up which works on a sub report or a sub form.
Open the subreport by itself
In the "Report Footer" of the subreport put a new text box and make its visible property NO.

then put the formula in there like
=sum([yourfield])
Preview the subreport by itself to make sure the formula is working correctly.
close and save it.

Then on the main report simply refer to the New text box
using the builder.

here is a test one I just did
=[CustomersOrders subreport].[Report]![Text12]

Just make sure you reference the NEW text box and no the field

DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.

 

[jgarnick]

Thanks Doug--all that makes sense, but when I do exactly what you said above, the sum works fine on the subreport, but it does not display correctly on the report. It displays the number from the last line of the detail ??? I definitely have the correct field name in my expression--it's baffling!

jgarnick
jgarnick@aol.com

 

[jgarnick]

Ok, so I have figured out why it is displaying the last detail line. I have fields on the main report in the detail section and I have the subreport placed on top of the fields also in the detail section of the main report. I strategically lined up the fields on the subreport so that they display next to the fields on the main report (which is what I need them to do). So what is happening is for each detail line on the main report it is displaying the subreport which includes the report footer on the subreport that has the invisible sum expression. So when it gets to the main report's report footer, it displays the last run of the subreport's numbers. I know this is confusing! I just can't figure out a better way to do this!

Column 1 Column 2
Row 1 10 (9) 101 (98)
Row 2 23 (23) 5 (5)

Total 33 (32) 106 (103)

What I am trying to get is the totals in ( ). The numbers in ( ) are from the subreport.

jgarnick
jgarnick@aol.com