Report and Subreport Totals

[Dakota1]

Hi All!

I am trying to get a calculated total on my main report from my subreport. The subreport holds information on commission earned from the current week. It pulls from a query with RepName, OrderDate, and TotalCommission. I used the totals to group by RepName where OrderDate is Between DateAdd("d",-Weekday(Date()),Date()+2) And Date() and TotalCommission is summed. On my subreport, I created a text box with =Sum(SumOfTotalCommission) which is the new field created from the query in the report footer (WTDTotals). On my main report I cannot get it to come up as the correct total. It either comes up as the first piece of data in the SumOfTotalCommission field or #Error or #Name?. I have tried everything and would love to have someone else's input! Thank you for all of your help in advance!

 

[PaulO]

So, just so I understand, your subreport calculates and displays the correct value ... and you want a Text Field in the Main Report to display that same total is a pre-determined place?

 

[Dakota1]

Yes, the subreport displays the correct value. However, when I try to get it displayed on the main report I cannot get it to work. Is there a specific formula I should be using or something else? Thank you!

 

[vbaInet]

You can't use the Sum() function on a calculated value. It will only work if the calculation is in the Record Source of the report. So move the calculation to the query that is bound to the report and perform you calculation on that "bound" field.