By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,871 Members | 2,575 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,871 IT Pros & Developers. It's quick & easy.

Why isn't my Sum/Nz function working?

Seth Schrock
Expert 2.5K+
P: 2,941
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 #1

✓ answered by zmbd

Seth,
The VBA is a jack-hammer when a tack-hammer will do.
I noticed that you have the fields in the page footer... if you need the totals over the whole report, then you might try moving them to the report footer.
-z

Share this Question
Share on Google+
15 Replies


NeoPa
Expert Mod 15k+
P: 31,489
The question you should be asking yourself is :
Does every record have a value for [ItemPrice]?

If not, then Sum() will probably crash. Once it's crashed, trying to pass the result (which is never produced) into Nz() is not going to work. Try :
Expand|Select|Wrap|Line Numbers
  1. =Sum(Nz([ItemPrice],0))
Sep 4 '12 #2

Seth Schrock
Expert 2.5K+
P: 2,941
ItemPrice will always have a value, but SalesTax might not, so I changed it. No change however. Just to be on the safe side I changed the default control names from being the same as the field name to having the txt prefix just incase the function was trying to add up the control instead of the field. That didn't work either.
Sep 4 '12 #3

NeoPa
Expert Mod 15k+
P: 31,489
If it's not the data then how about the section the control's in? It's a bit hard to work with so little relevant information as to what you're working with Seth. If you're aggregating, it makes sense to explain where and in what context ;-)
Sep 4 '12 #4

Seth Schrock
Expert 2.5K+
P: 2,941
In the Detail section of the report I have the individual items sold listed, including the fields ItemPrice, SalesTax, TotalPrice with the textbox names being the same as the field name with the txt prefix. All of this data is coming from a query that doesn't have any aggregating being done in the query. The totals textboxes are in the Page Footer section of the report. Basically, the report is run from the Sales form. The query is designed to pull the items sold in that sale and I'm trying to create a receipt for those items. Hopefully that is the information that you need. Please ask if you need anything else.
Sep 4 '12 #5

zmbd
Expert Mod 5K+
P: 5,397
I know it sounds silly... did you double check that the names of the controls in the footer do NOT have the same names as any other controls on the form? I usually add a prefix like "ZTTL_", "ZSUM_", or "ZCNT_" to summery fields.

Seriously... had a report that I build when I first started doing counters/totals/etc... and it drove me nuts trying to figure out what the issue was... I had copied a form from a different database etc... wasn't until I was trying to run a VBA to calculate the sum instead that the debuger caught the issue.

It's like haveing the powercord unplugged by the cat when the tele wont turn on! People will swear on their late-great-grandma's grave it's plugged in...

-z
Sep 4 '12 #6

Seth Schrock
Expert 2.5K+
P: 2,941
I can't remember off the top of my head exactly what they are named but it is something like txtItemPriceTotal. I checked that last night. But you do give me an idea. I will try doing this in VBA and see if I can figure out what is wrong.
Sep 4 '12 #7

zmbd
Expert Mod 5K+
P: 5,397
Seth,
The VBA is a jack-hammer when a tack-hammer will do.
I noticed that you have the fields in the page footer... if you need the totals over the whole report, then you might try moving them to the report footer.
-z
Sep 4 '12 #8

zmbd
Expert Mod 5K+
P: 5,397
Is the [ItemPrice] a calculated field in a query?
-z
Sep 4 '12 #9

Seth Schrock
Expert 2.5K+
P: 2,941
I don't have the database in front of me, but I don't think that ItemPrice is a calculated field, but SalesTax and TotalPrice are.
Sep 4 '12 #10

zmbd
Expert Mod 5K+
P: 5,397
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
Expert 2.5K+
P: 2,941
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
Expert Mod 15k+
P: 31,489
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
Expert Mod 5K+
P: 5,397
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
Expert 2.5K+
P: 2,941
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
Expert Mod 15k+
P: 31,489
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

Post your reply

Sign in to post your reply or Sign up for a free account.