Adding a record in detail and a field from a subreport

[kdaniel7979]

Not sure if I am way off here. But.

Lets say I have a report with 5 deptments and some sales numbers. And
in this report I have added a subreport that shows some more sales
records where the depts are equal.

Can I add a summary of the sales in the Detail footer with a sales
field in the subreport? Or is this like apples and oranges?

Thx

Dan

 

[Allen Browne]

You can bring the total from the subreport back onto the main report.

To do that, you need a text box in the Report Footer section of your
subreport. This text box will have properties something like this:
Control Source =Sum([Amount])
Format Currency
Name txtTotalAmount

Now if the subreport control is named Sub1, you can place a text box on the
main report, and set its Control Source to:
=[Sub1].[Report]![txtTotalAmount]

That works, but it shows #Error for the case where the subreport has no
records, so you really need to test the HasData property of the report in
the subreport control, and use Nz() to handle any null. The result will be
something like this:
=IIf([Sub1].[Report].HasData, Nz([Sub1].[Report]![txtTotalAmount], 0),
0)

 

[kdaniel7979]

Thanks Allen. I don't think I was clear enough. I want to take a single
record from the subreport and add that to a sum in the main report.

Main Report
Record 1 $10
Record 2 $30
Record 3 $50

Subtotal $80

Subreport
Record 101 $15

What I'm looking for is the addition of $80 + $15 back into the main
report.

Is that possible?

Tnx

 

[Allen Browne]

If the subreport has a value in a text box named Text0, you can refer to it
in the main report as:

=IIf([Sub1].[Report].HasData, Nz([Sub1].[Report]![Text0], 0), 0)

 

[kdaniel7979]

This is how mine looks

=IIf([Reports]![SubReport - Costs -
Forex].HasData,Nz([Reports]![SubReport - Costs - Forex]![Text52],0),0)

I still get the #Name? error. Maybe I'm doing something else wrong.

 

[Allen Browne]

Suggestions:

1. The reference:
[Reports]![SubReport - Costs - Forex]
is incorrect. Subreports are not open in their own right, i.e. they are not
part of the Reports collection.

Instead, you need the name of the subreport control, followed by .Report so
you have a reference to the report in the subreport control. Replace that
text with:
[SubReport - Costs - Forex].[Report]

2. If that does not fix the problem:
- Open the main report in design view.
- Right-click the edge of the subform control, and choose Properies.
- On the Other tab of the Properties box, check the Name of the subform
control. It may not be the same as the name of the report that gets loaded
into the control (its SourceObject.)

3. If the name is right, is this text box in the same section as the
subreport?

 

[kdaniel7979]

Solution was Number 1.

Thank you very much Allen