Why isn't my Sum/Nz function working?

[Seth Schrock]

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

=Nz(Sum([ItemPrice]),0)

When I open the report, the textbox just says #Error. What am I doing wrong? I tried just doing

=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.

[NeoPa]

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 :

=Sum(Nz([ItemPrice],0))

[Seth Schrock]

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.

[NeoPa]

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 ;-)

[Seth Schrock]

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.

[zmbd]

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

[Seth Schrock]

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.

[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

[zmbd]

Is the [ItemPrice] a calculated field in a query?
-z

[Seth Schrock]

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.

[zmbd]

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

[Seth Schrock]

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.

[NeoPa]

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.

[zmbd]

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

[Seth Schrock]

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.

[NeoPa]

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.