DSUM Criteria to exclude a null field (1 Viewer)

eschrob

Registered User.
Local time
Yesterday, 19:43
Joined
Apr 28, 2019
Messages
24
I am trying to use a DSUM function in a form that I've got working with a few critieria. But I want to add one more criteria (with an 'And' function) for a field that usually contains a price (Currency) but may contain nothing (null). I want the DSUM to exclude the Null.

My function that returns the correct result looks like this

=DSum("[d Ext$ BidNeg-PA]","qry RFQ Main","[Account]=[AS] And [RFQYear]=[YS]")

When I add the last criteria to exclude the null I'm getting either the same result as above or an error.

=DSum("[d Ext$ BidNeg-PA]","qry RFQ Main","[Account]=[AS] And [RFQYear]=[YS] And [WW Price] <>null")

Any ideas?
 

plog

Banishment Pending
Local time
Yesterday, 19:43
Joined
May 11, 2011
Messages
11,613
Its like nails on a chalkboard. Do you really have a field called "d Ext$ BidNeg-PA"? Do you really have spaces in the query and field names? It hurts, it hurts. Just a little jab, I mean nothing super-personal by it. It just that its best to use only alphanumeric characters in names--no spaces, no special characters. Makes coding and querying a little easier--you wouldn't need the brackets around all the fields.

For your issue, try using:

...AND IsNull([WW Price])=False")

Back to your fields. These aren't necessarily wrong, its just red flags and I have to ask:

1. [d Ext$ BidNeg-PA] seems like data itself, which means it shouldn't be the name of a field. Normal fields are usually generic words (CustomerName, SalesDate, Mileage, etc.). That field makes me think you aren't storing or querying your data properly. What does "d Ext$ BidNeg-PA" represent? How many pieces of data do you have encoded in their?

2. Criteria that equates 2 fields of the same datasource ([Account] & [AS] and [RFQYear] & [YS]) is weird as well. Why would you need to set 2 pairs of fields equal to each other in criteria?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:43
Joined
May 7, 2009
Messages
19,169
=DSum("[d Ext$ BidNeg-PA]","qry RFQ Main","[Account]=[AS] And [RFQYear]=[YS] And Isnull([WW Price]) = False")
 

eschrob

Registered User.
Local time
Yesterday, 19:43
Joined
Apr 28, 2019
Messages
24
Its like nails on a chalkboard. Do you really have a field called "d Ext$ BidNeg-PA"? Do you really have spaces in the query and field names? It hurts, it hurts. Just a little jab, I mean nothing super-personal by it. It just that its best to use only alphanumeric characters in names--no spaces, no special characters. Makes coding and querying a little easier--you wouldn't need the brackets around all the fields.

For your issue, try using:

...AND IsNull([WW Price])=False")

Back to your fields. These aren't necessarily wrong, its just red flags and I have to ask:

1. [d Ext$ BidNeg-PA] seems like data itself, which means it shouldn't be the name of a field. Normal fields are usually generic words (CustomerName, SalesDate, Mileage, etc.). That field makes me think you aren't storing or querying your data properly. What does "d Ext$ BidNeg-PA" represent? How many pieces of data do you have encoded in their?

2. Criteria that equates 2 fields of the same datasource ([Account] & [AS] and [RFQYear] & [YS]) is weird as well. Why would you need to set 2 pairs of fields equal to each other in criteria?

Thanks for the input. I set up my database as a beginner and have learned a ton. My table/query/control names were not set up properly when I started. I've taken an online class and learned of the proper way for naming. So far, things are working fine and I've been able to "get away with these strange name. When I have time, I will reconstruct it and rename them properly (doing so slowly now).

Questions 1: d Ext$ BidNeg-PA is a field name that's calculated in the qry RFQ Main. It's the delta between current bid and current price (PA) * QTY. So this is not representing a formular within the DSUM criteria.

Question 2: the criteria [Account]=[AS]. [AS] is a combo box to select the Account. So the criteria is simply to only sum where the form's Account field = the account name in the [AS] combo box. It works very well.

All that being said, and I appreciate the advise ==>AND IsNull([WW Price])=False (and another posted the same), but it's still not working.
Since [WW Price] is a number (currency), I've also tried AND [WW Price] > .001 but that has no affect. (.001 is > Null right?), If I change to a high number like AND [WW Price] > .05 I get a different result. This is telling me that the [WW Price] doesn't contain Nulls, it contains blanks (I guess) which are different. So IsNull([WW Price])= False is not excluding them.

How do I exclude "blanks"?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:43
Joined
May 7, 2009
Messages
19,169
if IsNull() doesn't work for you, try using Nz() function:

...
And Nz([WW Price], 0) > 0"
 

eschrob

Registered User.
Local time
Yesterday, 19:43
Joined
Apr 28, 2019
Messages
24
I also tried [WW Price] <> """ but no luck with that either
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:43
Joined
May 7, 2009
Messages
19,169
check the Datatype of WW Price. numeric or text?
 

plog

Banishment Pending
Local time
Yesterday, 19:43
Joined
May 11, 2011
Messages
11,613
Can you upload a sample database?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:43
Joined
May 7, 2009
Messages
19,169
If you can upload a sample db, im interested of finding out why it is ignoring the criteria of dsum.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 00:43
Joined
Feb 19, 2013
Messages
16,553
Numeric for sure
just because it looks like a number, doesn't make it a number, just as visually there is no difference between a zls "" and null. SOunds like you have something the looks like a duck, but doesn't quack like a duck:)

to make sure surround your calculation with the cdbl function, or perhaps cLng

Note: as already mentioned, using certain characters in table field names that have specific meanings in vba or sql - $&-+* to name a few can still cause unexpected results in certain circumstances (not always), even with the use of square brackets. I've also seen situations when spaces are used and one of the words is a function - e.g. 'invoice date' - which can (but again not always) generate unexpected results.
 

eschrob

Registered User.
Local time
Yesterday, 19:43
Joined
Apr 28, 2019
Messages
24
That is great in
just because it looks like a number, doesn't make it a number, just as visually there is no difference between a zls "" and null. SOunds like you have something the looks like a duck, but doesn't quack like a duck:)

to make sure surround your calculation with the cdbl function, or perhaps cLng

Note: as already mentioned, using certain characters in table field names that have specific meanings in vba or sql - $&-+* to name a few can still cause unexpected results in certain circumstances (not always), even with the use of square brackets. I've also seen situations when spaces are used and one of the words is a function - e.g. 'invoice date' - which can (but again not always) generate unexpected results.
That is great input- i've used $ in some of my names. I will fix that.
I'll look at cdbl or cLng, thanks
 

eschrob

Registered User.
Local time
Yesterday, 19:43
Joined
Apr 28, 2019
Messages
24
If you can upload a sample db, im interested of finding out why it is ignoring the criteria of dsum.

Appreciate the assistance. I need to strip away the sensitive info in the database which will take some time. I will upload eventually. thanks again
 

eschrob

Registered User.
Local time
Yesterday, 19:43
Joined
Apr 28, 2019
Messages
24
Here is a stripped down version of the database. I know my control names are not appropriate- I'll fix that eventually.



In the Detail section of the form, there are controls based on the query that provide the data such as
a) WW Price
b) BidNeg (price)
c) Extended WW Price (which is WW Price * EAU)
d) Extended BidNeg Price
e) d ExtBidNeg- WW (diff between Extended Bid - Extended WWPrice

In the header, there's a
f) sum of Extended WW Price
g) sum of Extended Bid Price
h) sum([dExtBidNeg-WW])- delta of these two sums (colored RED) The sum in this field is correct. It ignores the blank fields where there is NO WW Price.

In the header,
1) please select RFQYr= 2021 (i deleted all previous years data for this upload)
2) please select Account= Customer (ignore Customer 1 and Customer 2)

The DSum (also in red) needs to equal the sum([dExtBodNeg-WW] (for now- once I get this fixed, I'll add more criteria*).
Current formula =
=DSum("[d Ext$ BidNeg-PA]","qry RFQ Main","[Account]=[AS] And [RFQYear]=[YS] And Nz([WW Price],0)>0")

When you right click on WW Price (when blank) and select = Blank, the numbers match. (this is effectively what I want to do in the DSUM- filter out those records where WW Price is blank (or null?).

Hope this make sense...
 

Attachments

  • Experiment.accdb
    2.9 MB · Views: 390

eschrob

Registered User.
Local time
Yesterday, 19:43
Joined
Apr 28, 2019
Messages
24
I even now updated my query to use Nz function to make sure WW Price has either 0 or a price >0.
But the Dsum still isn't giving me the result I need! Arghhhh
 

eschrob

Registered User.
Local time
Yesterday, 19:43
Joined
Apr 28, 2019
Messages
24
I actually think I got it. I had other filters in my working database that were confusing the results I expected. I think using the Nz function in my query is the way to go
 

eschrob

Registered User.
Local time
Yesterday, 19:43
Joined
Apr 28, 2019
Messages
24
Yep, I had other filters in my working database that I wasn't accounting for in Dsum. I think I'm set.

I have a lot to learn and continue to look for tutorials on line.
Thanks for your suggestions. Using Nz was a big help.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:43
Joined
Feb 19, 2002
Messages
42,981
Glad you got it worked out.

FYI:
Going back to your original try of "<> Null". Any value compared to null results in null which is interpreted as false. So
a <> null = false
null <> null = false also

The only way to get a comparison to null is to use the IsNull() function in VBA or the "Is Null" predicate in SQL.
 

Isaac

Lifelong Learner
Local time
Yesterday, 17:43
Joined
Mar 14, 2017
Messages
8,738
Technically you can use IsNull() in queries, even though it's VBA, since Access generously allows it - but given that it has to run VBA, it's probably slower than Is Null.
 

Users who are viewing this thread

Top Bottom