Sub Report Won't Total

[rschultz]

I created a subReport called "Other Status:" In it I have a field called Weigh_Net and in the
SubReport Footer I have a Text box called TEXT6 with =SUM([Weigh_Net]/2000) - that works.

Then in the Main Report Footer I have a textbox with:
=Sum([Reports]![MJs Report]![Other Status:]![Text6])

but I keep getting 0 as a results.

I remembered that Pat has said not to reference the Control but to sum the formula so I also
tried:

=Sum([Reports]![MJs Report]![Other Status:]![Weigh_Net])

in a textbox in the Report Footer of the Main report but I still get 0

What am I doing wrong?

 

[Rich]

=[Other Status]![Text6]

 

[rschultz]

okay that wsa a good start<grin> at least I'm getting a number now, not just 0, but it's just giving me the last number total for the subreport. Also I have another problem because I made the subreport visible so I could see what was going on and the subreport repeats several times, even though the other totals from the main form are okay but the subreport is screwy.

 

[rschultz]

okay, I found out why the subreport is repeating but I don't know how to stop it and I don't know how to get the total of the subreport.

The subreport repeats for every record in the main report. That is, on 11/01/2001 there are 63 records on the main report, so the sub report repeats 63 times. But for 11/03/2001 there are only 4 records on the main report so there are only 4 subreport repeats for that day. How do I stop the subreports from repeating for each record on the main report and how do I get the total of the subreport's weight? The formula:
=([Other Status:]![text6])
in the footer of the main report just gives me the amount of the last record total in the subreport. For instance: The last day's total on the subreport is 47 - so that's what appears in the text box on the main report's footer.