473,890 Members | 1,370 Online

# Why isn't my Sum/Nz function working?

2,965 Recognized Expert Specialist
I have a report that I'm trying to build as a receipt for my customers. The important field names are ItemPrice, SalesTax, and TotalPrice. I have three textboxes that will contain the total amount for each of these fields. I'm using the following as the Control Source
Expand|Select|Wrap|Line Numbers
1. =Nz(Sum([ItemPrice]),0)
When I open the report, the textbox just says #Error. What am I doing wrong? I tried just doing
Expand|Select|Wrap|Line Numbers
1. =Sum(ItemPrice)
, but I get the same result. I copied this from the NorthWind sample database. I used the Expression Builder to make sure I was typing things correctly, but that didn't help. I don't know what else to try or what information I need to give to make it easier to solve.
Sep 4 '12
15 11129
zmbd
5,501 Recognized Expert Moderator Expert
Hmmm.
I know that summing calculated results will give an error... if you are trying to do page by page totals then you will need the vba and read thru the following article as it has a step by step: http://office.microsoft.com/en-us/ac...001122444.aspx
-z
Sep 4 '12 #11
Seth Schrock
2,965 Recognized Expert Specialist
Interesting... I did't realize that the Page footer didn't support Sum() functions. I had only put it there because I wanted the total at the bottom of the page instead of at the end of the data, but I will move it and see if it changes the results.
Sep 4 '12 #12
NeoPa
32,584 Recognized Expert Moderator MVP
I think that's your problem Seth. Aggregation (Summing in this case) can only work across a defined group. A page is grouped by how much will fit in a certain length of space, so doesn't represent anything you can aggregate over. Essentially then you need to put the controls in the header of either a sorted group or the whole report.
Sep 4 '12 #13
zmbd
5,501 Recognized Expert Moderator Expert
If I want the grand total on every page, I place a text control in the report footer/header, set the data property, set the control to visible=no. Then in the page section insert another text control... usually change the lable to "Grand Total" or what ever and then set the data sorce to be the control in the footer and you could do the same as above for group totals, in the details section, etc so that th results are at the bottom of the page.

The link I pointed to has some clever ideas...

-z
Sep 4 '12 #14
Seth Schrock
2,965 Recognized Expert Specialist
So Simple. Nice to know this. I just cut and pasted the totals textboxes into the report footer and it worked. Thanks z and NeoPa.
Sep 4 '12 #15
NeoPa
32,584 Recognized Expert Moderator MVP
Z is absolutely correct, in that you can produce totals on a Page Header, but it requires working around the fact that a page is Not fundamentally a group that can be aggregated across. It helps to understand the basics even (especially) when using clever approaches that get around those fundamental limitations.
Sep 5 '12 #16