UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Help Using Nz In A Dsum Expression, Access 2016    
 
   
drewjames
post Dec 20 2017, 04:33 PM
Post#1



Posts: 15
Joined: 13-April 14



Hi Guys

I am trying to use the following expression but it contains null values, therefore i believe i need to encase with Nz. I have tried all ways of encasing the fields with Nz but cannot get anything to work.. Can anyone help or show me where to put Nz in this expression?

=DSum("[Order Quantity 2]","[qryTurkeyOrders2017]","[Size Ordered 2] = '4.0 kg'")


Thanks in advance
Go to the top of the page
 
theDBguy
post Dec 20 2017, 04:44 PM
Post#2


Access Wiki and Forums Moderator
Posts: 71,430
Joined: 19-June 07
From: SunnySandyEggo


Hi,

You could try using Nz() as follows:

=DSum("Nz([Order Quantity 2],0)", "qryTurkeyOrders2017", "[Size Ordered 2]='4.0 kg'")

or exclude null values with maybe something like:

=DSum("[Order Quantity 2]", "qryTurkeyOrders2017", "[Size Ordered 2]='4.0 kg' AND [Order Quantity 2] Is Not Null")

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
drewjames
post Dec 20 2017, 04:55 PM
Post#3



Posts: 15
Joined: 13-April 14



Hi

Thanks for the reply. I've tried both expressions and i still get a blank field, rather than a 0.. any other ideas? Thanks
Go to the top of the page
 
RJD
post Dec 20 2017, 05:04 PM
Post#4


UtterAccess VIP
Posts: 7,982
Joined: 25-October 10
From: Gulf South USA


Hi: PMFJI, but you might try creating a separate totals query to get that value as a test. If you can work that, then you can include the query in the object source query (with no join, since it should be a single value) or use DLookup. Or that might enlighten you as to why you are not getting the value you want with the DSum.

Failing these approaches, perhaps you could post a db with the relevant tables and queries (no sensitive data, zipped) and someone could take a look. There might be something subtle that we can't see without the objects in hand ...

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
drewjames
post Dec 21 2017, 05:37 AM
Post#5



Posts: 15
Joined: 13-April 14



Hi thanks for the reply. I have created a seperate query just to show the totals for each weight of turkey. Therefore the query has 4 colums:
Size Orderered
Order Quantity
Size Ordered 2
Order Quantity 2

I have null values in the last two colums ("size ordered 2" and "order quantity 2") as most customers only make a single order. It is only a few customers that make two orders, eg. they will order a 4kg turkey and a 5kg turkey, each with quantity of 1.

when is use the following expression to try and sum the totals for order quantities of the second order for 4.0kg only i get #error

=DSum("Nz([Order Quantity 2],0)", "qryTurkeyOrders2017-totals", "[Size Ordered 2]='4.0 kg'")

Is this because i have null values in the text fields for "size ordered 2" ?


Appreciate any help! I may have to just sum these totals up manaully!
This post has been edited by drewjames: Dec 21 2017, 05:37 AM
Go to the top of the page
 
Jaiket
post Dec 21 2017, 05:52 AM
Post#6



Posts: 125
Joined: 3-May 17
From: France


I am not an expert...especially at queries.
I would have hoped Order Quantity 2 would default to 0. Is it an integer datatype? Sorry if this is wrong in query context.

--------------------
UA = Unsurpassable Assistance. Couldn't do it without you.
Go to the top of the page
 
drewjames
post Dec 21 2017, 06:02 AM
Post#7



Posts: 15
Joined: 13-April 14



Yes "Order Quantity 2" is set as integer data type.
Go to the top of the page
 
Jaiket
post Dec 21 2017, 06:40 AM
Post#8



Posts: 125
Joined: 3-May 17
From: France


I hope I'm not wasting your time.. I am learning this on the fly, but I can see why you may be under pressure, turkeys, 21st dec..

https://support.office.com/en-us/article/DS...6f-386056e61a32

QUOTE
Any field that is included in criteria must also be a field in domain; otherwise, the DSum function returns a Null.

I think that means that [Size Ordered 2] must be part of qryTurkeyOrders2017-totals.

There are other warnings of this type on the linked page.

--------------------
UA = Unsurpassable Assistance. Couldn't do it without you.
Go to the top of the page
 
RJD
post Dec 21 2017, 08:43 AM
Post#9


UtterAccess VIP
Posts: 7,982
Joined: 25-October 10
From: Gulf South USA


Hi: I had hoped you would have at least shown us the query you created. Better still, a db with the relevant objects with some test data to show us the issue (with no sensitive data, zipped). I think the solution will be apparent once we can see the db and run tests.

Also, you are using a dash in the domain of the last DSum you posted. That is almost always a bad idea, especially when you do not enclose the domain name in square brackets. But what we really need to have is the part of your db that is causing your issue. Because I don't think you need a DSum at all anyway.

I am also concerned about your table design, with two orders in a single record... but, as I said, we need to see your db (relevant objects and some data) to diagnose this...

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st January 2018 - 03:41 PM