473,890 Members | 1,429 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Why isn't my Sum/Nz function working?

Seth Schrock
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 #1
15 11128
32,584 Recognized Expert Moderator MVP
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
2,965 Recognized Expert Specialist
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
32,584 Recognized Expert Moderator MVP
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
2,965 Recognized Expert Specialist
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
5,501 Recognized Expert Moderator Expert
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...

Sep 4 '12 #6
Seth Schrock
2,965 Recognized Expert Specialist
I can't remember off the top of my head exactly what they are named but it is something like txtItemPriceTot al. 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
5,501 Recognized Expert Moderator Expert
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.
Sep 4 '12 #8
5,501 Recognized Expert Moderator Expert
Is the [ItemPrice] a calculated field in a query?
Sep 4 '12 #9
Seth Schrock
2,965 Recognized Expert Specialist
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

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

Similar topics

by: Dfenestr8 | last post by:
I started off trying to use bsddb with my standard mandrake 9 python 2.2.1 package. The shell reported back no such module. Strange, thinks I. I thought it was standard in the package, it was when I was using Windows. So I uninstalled Python, downloaded the latest python 2.3 and compiled it from source. Now when I try to import bsddb, I get this error...... >>> import bsddb
by: David Shaw | last post by:
Hi, I'm writing a fun little program to play "Petals Around the Roses" with... I need to seed my random numbers so that they won't be the same every time I run the program, but my compiler won't let me. Here's the output my compiler generated...: -- Compiler: Default compiler Executing g++.exe... g++.exe "C:\Documents and Settings\rmarkoff\Desktop\roses.cpp" -o "C:\Documents and Settings\rmarkoff\Desktop\roses.exe"
by: Sunshine | last post by:
Pretty new to javascript so please help. Why isn't this working in my validateform()? if (document.This.strOld.value.compareTo(Session("strPassword")) != 0) { alert("Current password incorrect"); return false; } If I write out Session("strPassword") the value is 'password'. What am I doing wrong?
by: Chris | last post by:
Hi, I'm stumped and could use some suggestions. I'm trying to serialize some data to an XML fragment in string form, then deserialize it from a string back to an XML fragment, from which I will rehydrate my class. I've built a little Windows forms test app to see if my approach will work. It doesn't. When I step through the code in the debugger, I see that the MemoryStream is null, and the string variable "xmlFragment" is an empty
by: interpim | last post by:
Just a quick exercise from my C programming book, that I can't figure out why it isn't working properly. It kinda works but im getting wierd output. It is supposed to remove the vowels from text. #include <ctype.h> #include <stdio.h> int isvowel(int letter); int main(void) { int letter;
by: Christina Androne | last post by:
I've read the docs and evrything seems fine. Then what I am missing ? This is the code snippet: try {
by: Dave | last post by:
I have a simple master page and it won't seem to work: <%@ Master Language="C#" AutoEventWireup="true" CodeFile="z.master.cs" Inherits="z" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http:// www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server">
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.