473,703 Members | 2,333 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

NZ Assistance with DSum

I seemed to be having problems with structuring the use of NZ with a
DSum expression. Having tried numerous variations of the expression
without success, I'm asking for assistance.

First some background (Access 97) - the DSum expressions are being
used in grand total text boxes on the footer of a subform. And when
viewing the subform in the linked main form the two grand total boxes
display #Error if no entry had been made. Those with entries are
calculated correctly and properly shown.

As a test, I have used =NZ([TotalSales], "0") in one of the grand
total text boxes, and yes, it does provide a "0". Incoporating NZ into
the expression allows the DSum part to work properly; however, there
is no resulting "0" on blank sales entry subforms. One of the
expressions used is: =NZ(CCur(DSum("[TotalSales]", "tblSales","IDS ales
= " & [IDSales])),"0"). Yes, I have tried the expression without using
" around the 0 and without CCur, and countless other ways it seems,
but to no avail.

The TotalSales refers to the field name of a related text box which
holds the value of =[Units]*[SalesPrice]. The field name of the grand
total text box on the subform footer is GTTotalSales.

Any assistance will be appreciated. Thanks.
Nov 12 '05 #1
2 11585
Several aspects to this, Dalan.

1. Drop the quotes. They are only relevent to Text data, and I think you are
working with numbers, so you need:
=Nz([TotalSales, 0)

2. The DSum() error. Your 3rd argument is
"IDSales = " & [IDSales]
That's fine if IDSales has a value. If it doesn't the 3rd argument becomes
just:
ID Sales =
which naturally doesn't work. The solution is to provide some value for the
Null case so the argument can be evaluated. Assuming ID Sales is an
autonumber starting from 1 (so there is no zero), you could use:
"IDSales = " & Nz([IDSales], 0)

3. Can new entries be made in the subform? If not, and there are no matching
entries to display, the subform goes completely blank. If you are seeing
that kind of thing, Nz() won't solve the problem. You will need to use an
IIf() expression such as:
=IIf(IsError([TotalSales]), 0, Nz([TotalSales], 0))

4. If there are no matches, DSum() returns Null. CCur() can't handle null,
so you need to do the Nz() bit before the CCur():
=CCur(Nz(DSum("[TotalSales]", "tblSales",
"IDSales = " & Nz([IDSales], 0)), 0))
Actually, if you set the Format property of this text box to Currency, you
can probably drop the CCur() bit.

An alternative:
Create a query into the subform's table.
Type this expression into a fresh column, Field row:
Amount: [Units] * [SalesPrice]
Use this query as the the RecordSource of your query.
You can now display the Amount on each row (if desired), and include a text
box in the subform bound to:
=Sum([Amount])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Dalan" <ot***@safe-mail.net> wrote in message
news:50******** *************** ***@posting.goo gle.com...
I seemed to be having problems with structuring the use of NZ with a
DSum expression. Having tried numerous variations of the expression
without success, I'm asking for assistance.

First some background (Access 97) - the DSum expressions are being
used in grand total text boxes on the footer of a subform. And when
viewing the subform in the linked main form the two grand total boxes
display #Error if no entry had been made. Those with entries are
calculated correctly and properly shown.

As a test, I have used =NZ([TotalSales], "0") in one of the grand
total text boxes, and yes, it does provide a "0". Incoporating NZ into
the expression allows the DSum part to work properly; however, there
is no resulting "0" on blank sales entry subforms. One of the
expressions used is: =NZ(CCur(DSum("[TotalSales]", "tblSales","IDS ales
= " & [IDSales])),"0"). Yes, I have tried the expression without using
" around the 0 and without CCur, and countless other ways it seems,
but to no avail.

The TotalSales refers to the field name of a related text box which
holds the value of =[Units]*[SalesPrice]. The field name of the grand
total text box on the subform footer is GTTotalSales.

Any assistance will be appreciated. Thanks.

Nov 12 '05 #2
"Allen Browne" <ab************ ***@bigpond.net .au> wrote in message news:<Od******* *************@n ews-server.bigpond. net.au>...
Several aspects to this, Dalan.

1. Drop the quotes. They are only relevent to Text data, and I think you are
working with numbers, so you need:
=Nz([TotalSales, 0)

2. The DSum() error. Your 3rd argument is
"IDSales = " & [IDSales]
That's fine if IDSales has a value. If it doesn't the 3rd argument becomes
just:
ID Sales =
which naturally doesn't work. The solution is to provide some value for the
Null case so the argument can be evaluated. Assuming ID Sales is an
autonumber starting from 1 (so there is no zero), you could use:
"IDSales = " & Nz([IDSales], 0)

3. Can new entries be made in the subform? If not, and there are no matching
entries to display, the subform goes completely blank. If you are seeing
that kind of thing, Nz() won't solve the problem. You will need to use an
IIf() expression such as:
=IIf(IsError([TotalSales]), 0, Nz([TotalSales], 0))

4. If there are no matches, DSum() returns Null. CCur() can't handle null,
so you need to do the Nz() bit before the CCur():
=CCur(Nz(DSum("[TotalSales]", "tblSales",
"IDSales = " & Nz([IDSales], 0)), 0))
Actually, if you set the Format property of this text box to Currency, you
can probably drop the CCur() bit.

An alternative:
Create a query into the subform's table.
Type this expression into a fresh column, Field row:
Amount: [Units] * [SalesPrice]
Use this query as the the RecordSource of your query.
You can now display the Amount on each row (if desired), and include a text
box in the subform bound to:
=Sum([Amount])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Dalan" <ot***@safe-mail.net> wrote in message
news:50******** *************** ***@posting.goo gle.com...
I seemed to be having problems with structuring the use of NZ with a
DSum expression. Having tried numerous variations of the expression
without success, I'm asking for assistance.

First some background (Access 97) - the DSum expressions are being
used in grand total text boxes on the footer of a subform. And when
viewing the subform in the linked main form the two grand total boxes
display #Error if no entry had been made. Those with entries are
calculated correctly and properly shown.

As a test, I have used =NZ([TotalSales], "0") in one of the grand
total text boxes, and yes, it does provide a "0". Incoporating NZ into
the expression allows the DSum part to work properly; however, there
is no resulting "0" on blank sales entry subforms. One of the
expressions used is: =NZ(CCur(DSum("[TotalSales]", "tblSales","IDS ales
= " & [IDSales])),"0"). Yes, I have tried the expression without using
" around the 0 and without CCur, and countless other ways it seems,
but to no avail.

The TotalSales refers to the field name of a related text box which
holds the value of =[Units]*[SalesPrice]. The field name of the grand
total text box on the subform footer is GTTotalSales.

Any assistance will be appreciated. Thanks.


Thanks again Allen for your response and guidance. I will try your
suggestions today and hopefully will resolve the problem. I wanted to
let you know that I was originally using a query to supply the DSum
expression totals on the subform, but . . . when adding a new sales
record to the subform while in the main form an error message would
occur: Syntax Error (missing operator) in query expression '[IDSales]
='. The only way out was to use Ctrl-Alt-Del. However, later I
discovered that after clicking OK on the error messsage about a dozen
times, the message would disappear and was able to proceed to enter
sales data on the subform (after about 7 clicks or so #Error appeared
in all fields except the date ones - strange?).

I'm still using the query for supplying the information to run the
reports, but there is a difference in the DSum expression used:
=DSum("[TotalSales]","tblSales ", "IDSales = " & [IDMain])

Best regards, Dalan
Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2512
by: Rolan | last post by:
I'm using Access 97 and need some assistance in sorting out a proper DSum expression. It relates to a subform (sfrmCost) from which I'm wanting to extract the grand total of any and all data rows that have the same primary IDReference, and have it post as an accumulative sum in one text box (TotalCost) on the main form (frmMain). There are actually three columns across in each row which contain number values and I have been able to only...
0
3351
by: Rolan | last post by:
I'm using Access 97 and need some assistance in sorting out a proper DSum expression, or maybe even DCount might be an alternative. I have tried numerous combinations, but with no apparent success. The DSum function relates to a subform (sfrmCost) with a text box (RefCost) which is intended to be the container for a total from a query (qryItems). The query has three number columns from the table (tblItems) of which I have an expression...
1
4799
by: Dalan | last post by:
I have tried both methods of using DSum and creating a Function to address summing some number columns, but to no avail. Since this has been a popular topic over the years, I'm sure I'll receive some guidance. This is related to Access 97 and involves my redoing a miscellaneous purchase and sales form. A main form (frmMain) is used that contains summary information of the item. I incorporated two continuous subforms (sfrmPurchases) and...
1
4154
by: Alex | last post by:
Acc 97 Hi, I have the following in my query which works well if it is all set to numbers on table design. But what I want to do is where it states MC (short for machine name) use the actual machine name of which there are 5 off. therefore, on the table design I flick it over to text mode.
3
3763
by: Mark Reed | last post by:
All, I have never used this function before and am not sure it what I need. Just to clarify, I have a report based on a query which has amoungst other field, wk, parea & packs_req. What I am trying to do is create a subreport for the main report which gives a generalised breakdown of the main report. For the gender of 'Ladies', there may be several parea's so I want to sum the packs_req for ladies only. (Gender is not a field I can use,...
1
2174
by: gbb0330 | last post by:
Hi All i am trying to calculate Quantity on hand in a unbound textbox in a form with a subform QonHand=Qreceived - TotalQSold TotalQsold=QsoldOnEbay+QsoldThruOtherChannels here is my expression:
33
8227
by: potassium flower | last post by:
I am trying to create a food order system for a restaurant. I have tried using both the DSum and Sum functions to calculate the total cost of an order. The total cost is a textbox on the form tblOrder (and a field in tblOrder) and the list of meals along with their costs are in the tblOrderDetail Subform (the columns in this form come fromt two tables - tblOrderDetails and tblItems). I have been putting the DSum / Sum in the control...
1
3859
by: danielgoss | last post by:
Hi I have a report that has loads of textboxes that calculate things based on the value on another textbox in the report. I have put a hidden textbox on my report that gets its value from an inputbox when the report opens. I then want to use the value inputted in a DSUM calculation on the same form. The hidden textbox is called tbHolddate, its control source is: =InputBox() The textbox that calculates a value using a DSUM control...
3
2074
by: majapa | last post by:
To start, here is my DSum expression: Develop: Nz(DSum(". & "] + . & "]","CSO","='Ability to Develop'")*-1,0) I actually have 4 of these, each one with a different option for . For more context, the . part is drawing from a separate reference table which cycles through plugging in the name for each DB_ and Ben_ field in the CSO table. What I want to do is add another layer of criteria, the field which exists in the same CSO table. I'd...
0
9121
jinu1996
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...
1
9017
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8962
tracyyun
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...
0
7867
agi2029
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...
1
6588
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5922
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();...
0
4687
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3123
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
2
2450
muto222
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.