Problem passing line total from sub report to report

[tbjmobile@gmail.com]

Hello Everybody,


Can someone help please? I have started to create a report which will be a client monthly account. I have embedded a sub-report on it for work carried out during the month. The common field is customer code (cuscode). The report and sub-report are grouped therefore by cuscode and I'm working on forcing a page break after each cuscode group.
I understand that I need to generate a total in the sub-report footer and I've seen it on a Youtube tutorial and a description on The Microsoft Office \ Access help site. I can't get it to work and so I can't move forward to passing it to the main report.
The line total on the work sub report is a calculated control called 'TotJobRetail' with the expression =[RetailMats]+[HrsValue]+[CashOutNoProfit]
The line totals are calculated correctly.
On the footer I have tried =Sum([RetailMats]+[HrsValue]+[CashOutNoProfit]) which as far as I can see should do the trick.
If anyone can help, please be aware that I am still very new to Access so please explain in as much detail as you can.
Thanks.

[June7]

That should work, unless any of those referenced fields are really textboxes with calculations.

What happens - error message, wrong results, nothing?

If you want to provide db for analysis, follow instructions at bottom of my post.

[tbjmobile@gmail.com]

Hi June7 and thanks.
The other referenced fields are indeed text boxes with calculations. I thought I was better doing it that way than calculating the fields in the table.

[June7]

Aggregate calcs must reference fields, not controls.

Calculating in table Calculated type field is an option although many will advise against that.

Either do the intermediate calcs in table/query or repeat them in the aggregate calc.

[tbjmobile@gmail.com]

O.k., thank you, I'll try the latter first.

[tbjmobile@gmail.com]

If I place all the calculations of the referenced fields in the =sum expression in the footer, does that mean I'll have to put all the controls that they reference in the footer as well?

[June7]

No part of the aggregate expression can reference controls.

[tbjmobile@gmail.com]

Got it, thanks.

[tbjmobile@gmail.com]

Sorry, no I haven't got it! What I mean is that to make the calculated 'total' control in the sub report function, I had to place all of the other controls that it referenced into the sub report somewhere. So do I have to put them in the footer as also. I suppose I'll find out as I try though, just thought it might be a rule I should know.

[tbjmobile@gmail.com]

I do understand what you said now, apologies. The aggregate expression must be a field directly from the table which has been calculated, or from a query that queries the fields in the same manner.