Sum function on report not working

[dzirkelb]

I have a report created where the page uses a query as the back end, all works well. I need to add a text box with the summed up values; however, I keep getting the #error for the result of my sum function.

I am trying to Sum up one field called SCHDQTY. On the textbox I am trying to put the total into, I have this as the control source:

=Sum([SCHDQTY])

and I get the #Error message when I go into preview view.

What am I doing wrong?

 

[vbaInet]

Are you sure the textbox doesn't contain a Calculated value?

 

[dzirkelb]

The SCHDQTY field / text box is just a result from the query, a regular field with no calculations on it. Is that what you are asking?

 

[vbaInet]

Is that what you are asking?

Correct!

I believe you're trying to Sum() inside a Page Header or Page Footer section. The Sum() function doesn't work in those sections so you will need VBA for that. Have a look at this link:

http://office.microsoft.com/en-us/access-help/summing-in-reports-HA001122444.aspx

 

[dzirkelb]

You are correct, the sum is in the page footer section, and moving it to the detail works as expected, but is not where it needs to be as I need a total of the detail, not the total of each line.

I looked at the link provided but I didn't notice anything that I was doing different then what they said to do, besides it works for the example the provided and does not work for me. Could it be that this is an access 2007 database and the link provided is for 2002/2003 mdb's?

 

[vbaInet]

I looked at the link provided but I didn't notice anything that I was doing different then what they said to do, besides it works for the example the provided and does not work for me. Could it be that this is an access 2007 database and the link provided is for 2002/2003 mdb's?

You haven't looked at it properly. Like I mentioned, you need VBA (i.e. code) to perform the Sum. The section is called Placing sums in the page header or footer and it explains how to Sum using code. There's no other way to Sum() in a Page Footer without code.

 

[dzirkelb]

I placed it in the report footer and that works fine, thanks.

 

[vbaInet]

Pleased to hear.