Pulling data from subreport's detail area to report's detail area

[hedgracer]

I have a subreport detail area that has the following detail:

Box SumOfYTDConverted
5700 1200.00
5720 1699.00
5740 2000.00

The subreport is named Sec_Combined_Test.

In my main report I have a the subreport named Sec_Combined_Test. It is small and not visible. I am trying to do the following in a particular textbox of the main report:

=IIf([Sec_Combined_Test].[Report]![Box]="5700",[Sec_Combined_Test].[Report]![SumOfYTDConverted],0)

This always results in 0. If I just do =[Sec_Combined_Test].[Report]![Box] I get 5740 in the textbox. How can I get the SumOfYTDConverted number for 5700 in this textbox from the subreport? Any help is greatly appreciated.

Dave

 

[dhookom]

Are there more than one text box in the detail section of the subreport? If so, I'm not sure how you can reference any other than maybe the last one. You can't "query" these.

You could probably have a report footer of the subreport to calculate the value and then reference the single instance of the value from the main report.

Duane
Hook'D on Access
MS Access MVP

 

[hedgracer]

Are there more than one text box in the detail section of the subreport? If so, I'm not sure how you can reference any other than maybe the last one. You can't "query" these.

There is not more than one text box in the detail section of the subreport. Box and SumOfYTDConverted are completed by reading rows from a table named Sec_Combined_Test. That is the reason I figured I could do the if statement.

You could probably have a report footer of the subreport to calculate the value and then reference the single instance of the value from the main report.

This was my second option. Do a report footer (or page footer) for each box and then reference that in the if statement. I do not know if that is possible.

Dave

 

[dhookom]

I meant to ask if there are multiple records in the subreport. If this is the case, I expect you need to use a Report or Group footer section (no page footer). Your control source in the Report Footer section might be something like:

=Sum(Abs([Box]="5700") * [SumOfYTDConverted])

This assume the Box field is a text value rather than numeric.

Duane
Hook'D on Access
MS Access MVP