Grand Total of a Running Sum

[Trodelphin]

have an invoice report set out like so

Heading
Description............Price....Quantity...a text box with "=[Price]*[Quantity]



however getting a grand total seems a problem
my totals tool only allows me to get a count records since sum is greyed out

any ideas ?

 

[boblarson]

In the report footer, place a text box with this control source:

=Sum([Price]*[Quantity])

make sure you don't have any controls named Price or Quantity on the report (if you do, change those to txtPrice and txtQuantity) and you refer to the field names, but you have to recreate the formula in the footer or else it won't work right - you can't refer to a control in this instance.

 

[Trodelphin]

given Price and Quantity are Control Sources for those fields i am assuming you are saying to relabel thier name under "other" property on the report ?

 

[boblarson]

given Price and Quantity are Control Sources for those fields i am assuming you are saying to relabel thier name under "other" property on the report ?

Yes, that would be the case. But if you have Name AutoCorrect turned on, when you change those and save it, it will change your formula to use that (and you don't want that, you want the names of the fields). Make sure Name AutoCorrupt (AutoCorrect) is turned off on all of your databases.

 

[Trodelphin]

ok i see what you mean but what if i want to then add the results of that box to another ?
say i have 2 running Sums that i want totaled

 

[boblarson]

ok i see what you mean but what if i want to then add the results of that box to another ?
say i have 2 running Sums that i want totaled

A control source of say this:

= Sum(Nz([Price],0)*Nz([Quantity],0)) + Sum(Nz([SomeOtherField],0)

 

[Trodelphin]

would i be correct in assuming that
=Sum([Price]*[Quantity])
would capture teh results of
a varing amount of =[Price]*[Quantity] in a column ?

cheers for help too btw

 

[boblarson]

Yes, that is the case. Now I also put in the NZ function for everything to handle nulls so you don't end up with a problem because adding a null to a number will result in a null.

 

[Trodelphin]

hmm still seem to be just generating an error
spot anything ?

 

[Trodelphin]

ah i see it HAS to be right at the footer

 

[vbaInet]

If that isn't working peform a Price * Quantity in the record source (not the report), then perform a Sum() on that field.

Edit: yes Bob mentioned the footer.

 

[Trodelphin]

yeah i had it in the Page Footer but it has to be in the Report footer

 

[vbaInet]

Page Footer or Report Footer, it doesn't matter as long as it's in a Footer.

 

[Trodelphin]

Hmm i get an error as soon as i move it to the Page footer but works fine in the report footer

 

[vbaInet]

Follow suggestion in my first post (i.e. #11).

 

[Trodelphin]

your post 11 you mean a Sum*Quantity query ?

 

[vbaInet]

Create an alias field in your query, perform the Price * Total Hours in there, then Sum that field. That is:

Total: [Price] * [Total Machine Hours]

in the query. The query will become the record source of your report, if you've not already done so.

 

[Trodelphin]

interesting error in my Subreport now :/
seems to be Quadrupaling any result

 

[vbaInet]

Are you summing inside the subreport or on the main report?

 

[boblarson]

Page Footer or Report Footer, it doesn't matter as long as it's in a Footer.

it can't be in the page footer. It DOES make a difference.