Sum in Footer - won't work?

[Lol Owen]

Hi all, got a puzzler. have greated a template report which is opened, the record source changed programmatically to an SQL statement and printed. That all works fine, however I'm just trying to get a simple Sum function to work in the footer and it won't! On the report I have, amongst others, fields "Quantity", "SellPrice" and "Total" where "Total" is a straight multiplication of "Quantity" * "SellPrice". I've put a text box in the footer, called GrandTotal, ste it's RunningSum property to OverAll and it's control source to =Sum([Total]) or even =Sum([Quantity]*[SellPrice]). All i keep getting is #Error. Any thoughts please?

Cheers, Lol

 

[dopedealer]

As you are changing record scources through SQL are you checking to see the field names are exactly the same? just a thought. Also where are you putting the code? In the Activate event of the Report would be a good place.
Chris

 

[astarbyfar]

RunningTotal

I havent got a relevant example handy but it sounds as though you are not creating a detail section i.e. a section for each Product/Item you wish to calculate. If you post the database ill try and have a look and ATTEMPT to explain to you!

 

[Lol Owen]

I've created the report using exactly the same names as are supplied by the SQL statement and they all appear on the report just fine. The code is actually behind a button on an Access form, again no problems with that. All that is happening when I can get anything to appear in the GrandTotal box in the footer is the value for the last item in the list. Unfortunately the database stands at about 1 meg so far so even compressed and zipped I don't think it will make the 100kb file limit!

Cheers, Lol

 

[Rich]

Which footer?

 

[Lol Owen]

Rich, I've managed to strip the database down so it can be posted. The footer I mean is the report footer.

Cheers, Lol

 

[pbaldy]

Move it to the report footer instead of the page footer, and rename the textbox currently named "Total" (Access gets confused and doesn't know if you want the data field or the control, since they're both named "Total").

 

[Lol Owen]

Paul, big favour to ask, could you move it and re-post it for me please? In all honesty I haven't had a lot to do with reports and I'm having trouble seeing the wood for the trees at the moment! Am also banging my head on an Excel problem so any small help you can give on this one would be great.

many thanks, Lol

 

[Lol Owen]

Paul, ignore that message, got it sorted! Thanks for your suggestion!

Cheers, Lol

 

[pbaldy]

Sorry, wasn't ignoring you, I just now got back to this thread. Glad that sorted it out.