Solved Adding Total to Page Footer in Report

[Bean Machine]

Hi all! I am using a textbox to try and output the total cost of the items in my report I generate but I simply get the text "#Error" when testing. In the "Control Source" section of my text box ("txt_Tot") I am using to display the total, I have "=Sum([Amount])" but this does not seem to work. Any help would be greatly appreciated as I am not sure what the problem is. Thanks!

 

[plog]

Without a copy of the database these are the things to check:

1. Is their a field in the datasource of the report called [Amount]?

2. If so, is [Amount] a numeric field?

3. If so, are there actual values in [Amount] or are all records Null?

 

[Bean Machine]

There is a datasource called [Amount], all [Amount] fields are filled with currency values, and it is a numeric field yes. It's worth noting I have a text box in the "Detail" part of my report that simply divides the [Amount] and [Ordered] amounts to find the exact cost per item ordered with the "Control Source" for it being "=[Amount]/[Ordered]" and works fine.

 

[plog]

Iterative programming--start with something that works and slowly tweak it until you get what you want.

Add a new [Amount] field to your details section. Make sure it works.
Move that new field to a Group Header/Footer section. Make sure it works.
Change the data source to Sum([Amount]). Make sure it works.
Move that field the page footer. Make sure it works.
Move that field to the Report footer. Pray that it works.

 

[Bean Machine]

Okay I'm extremely stumped now. It works completely fine when I have exactly what I described in the "Detail" part of the report, but not the "Page Footer" part of the report. Any ideas on why this occurs?

 

[Bean Machine]

I failed to mention that it still picks up the [Amount] field fine when in the Page Footer, but using the Sum expression for some reason causes an error.

 

[arnelgp]

is this in Continuous form?
if not then you should use DSum() function.

 

[Bean Machine]

is this in Continuous form?
if not then you should use DSum() function.

Just took a look at the usage of the "DSum()" function and am a little confused. I tried doing "=DSum([Amount], [qry_Search])" but it doesn't work. Maybe I'm missing something, its late where I live so I also might just be tired. I appreciate any support.

 

[arnelgp]

=DSum("Amount", "qry_Search")

 

[Bean Machine]

Ah my mistake, this worked wonders and has solved my dilemma, thank you so much!

 

[arnelgp]

sleep tight

 

[Pat Hartman]

Page breaks are not in the "totals" hierarchy of a report and so if you want page totals, you need to calculate them yourself. You can use code in the detail format event to accumulate the total. You have to also have code in the detail retreat event because you will need to subtract the last item or it will be included twice since Access runs the format event but if there is no room on the page to print it, it then runs the retreat event. You also need to clear the accumulator in the format event of the page header or footer.