Setting Criteria to 'Is Not Null' in DSum Statement

[David Morgan]

= = = =
= = = =

In the DSum statement below, how do I change the 'Yes'
(in 'Action1 = Yes'), to Is Not Null? I need to set the criteria to
all records where the Action1 fields containg data.

=DSum("Pagecount", "YourTable", "Action1 = Yes")

Thanks.

================================================
Reply to: dm1@iname[dot]com (edit appropriately)

[Danny Lesandrini]

I just had trouble with that last week and I took
a different route. I replaced the table, "YourTable"
with a query, "YourQuery" where I inserted the
criteria Is Not Null.

I didn't research it and I might be wrong, but I think
that the DSum function can't handle Nulls.

How much is 1 + Null?
Answer: Null

As I said, I didn't waste much time on it because I
was once told that Nulls cause sum functions to
behave badly, since it's hard to say what 1 + unknown
evaluates to. I found it faster to just handle that in
the query used with the Domain Function DSum.

Just my opinion. Open to correction if someone else
knows what's going on with DSum and Nulls.
--

Danny Lesandrini
dlesa...@hotmail.com

David Morgan <d8...@hotmail.com> wrote in message
news:372358eb...@news.jps.net...

[Vanderghast]

Hi,

"Not IsNull(Action1)", relaying on VBA to be evaluated

or probably faster:

"Not Action1 Is Null", relaying on SQL to be evaluated

Hope it may help,
Vanderghast, Access MVP.

[Vanderghast]

Hi,

By SQL convention, 1+ NULL returns NULL, but aggregate functions REMOVE any
null before operation. SUM( FieldName) , or DSum("FieldName") will so
returns a not-null value UNLESS the field value IS NULL for ALL the records
(or that there is no record at all).

Vanderghast, Access MVP.

Danny Lesandrini <data...@goldeninter.net> wrote in message
news:IqKU2.7560$D66.1...@news2.randori.com...

> I just had trouble with that last week and I took
> a different route. I replaced the table, "YourTable"
> with a query, "YourQuery" where I inserted the
> criteria Is Not Null.
>
> I didn't research it and I might be wrong, but I think
> that the DSum function can't handle Nulls.
>
> How much is 1 + Null?
> Answer: Null
>
> As I said, I didn't waste much time on it because I
> was once told that Nulls cause sum functions to
> behave badly, since it's hard to say what 1 + unknown
> evaluates to. I found it faster to just handle that in
> the query used with the Domain Function DSum.
>
> Just my opinion. Open to correction if someone else
> knows what's going on with DSum and Nulls.
> --
>
> Danny Lesandrini
> dlesa...@hotmail.com
>
>

[Andrew Perrin]

I don't think so:

?(null=null)
Null

?(null<>null)
Null

?(14556<>null)
Null

You need to use either Is Not Null (using SQL) or Not IsNull(Action1).

ap

Henry Craven wrote:

> =DSum("Pagecount", "YourTable", "Action1 <> NULL")
>
> Henry Craven
> -------------------
> H_Cr...@bigpond.com
> --------------------------------------------------------------------------------
>
> David Morgan wrote in message <372358eb...@news.jps.net>...

> >= = = =
> >= = = =
> >
> >In the DSum statement below, how do I change the 'Yes'
> >(in 'Action1 = Yes'), to Is Not Null? I need to set the criteria to
> >all records where the Action1 fields containg data.
> >
> > =DSum("Pagecount", "YourTable", "Action1 = Yes")
> >
> >
> >Thanks.
> >
> >
> >
> >
> >
> >================================================
> >Reply to: dm1@iname[dot]com (edit appropriately)

--
-------------------------------------------------------------
Andrew J. Perrin - NT/Unix/Access Consulting - (650)938-4740
ape...@mcmahon.qal.berkeley.edu (Remove the Junk Mail King)
http://www.geocities.com/SiliconValley/Grid/7544/
-------------------------------------------------------------

[Henry Craven]

[Henry Craven]

Try It !

Henry Craven
-------------------
H_Cr...@bigpond.com
--------------------------------------------------------------------------------

Andrew Perrin wrote in message <3723F89A...@mcmahon.qal.berkeley.edu>...

> I don't think so:
>
>?(null=null)
>Null
>
>?(null<>null)
>Null
>
>?(14556<>null)
>Null
>
>You need to use either Is Not Null (using SQL) or Not IsNull(Action1).
>
>ap
>
>Henry Craven wrote:
>

[Vanderghast]

Hi,

If you try it in the QBE grid, you should observe that your statement is
AUTOMATICALLY CHANGED TO: IS NOT NULL.

Access definitively rewrites your statement <> NULL so that SQL will be
Field Is Not Null. That is observable, in the grid, immediately after you
push the enter key after the criteria <> Null. About Dxxx, I can only
ASSUME Access does the same conversion. Note that the theory says that NULL
is not equal to another NULL.That sentence itself just claims: Is it TRUE
that NULL is not equal to NULL. It is very hard to know what really really
JET will do with =Null and <> Null, since the sentence is translated using
the IS syntax.

In need of some laughs, VBA claims:

? Null = Null
Null

and

? Eval( "Null = Null")
-1 ' Null, this is True, that is!

also:

? Iff( Null, True, False)
False ' well, unless it is false, that is what I really mean!

But this is part of automatic data conversion (which seems to be dependant
of the function you are in progress to evaluate)

Hope it may help,
Vanderghast, Access MVP.

Henry Craven <IUnk...@d.com> wrote in message
news:yJUU2.19811$MB3....@newsfeeds.bigpond.com...

[Michel Walsh]

[Henry Craven]

I have no quibble with the fact that in Code, SQL Statements,The Criteria
of the QBE Grid one uses "Is Not Null" and/or "Not IsNull()" et al.

However.
That does not belie the fact that using the Domain Function

=DSum("Pagecount", "YourTable", "Action1 <> NULL")

evaluates correctly.

As a custom function, I presume the MS Access programmers
decided to make it easy for the users by using more conventional
( Excel ?) like syntax and handle things internally. - well and good.

When David Morgan asks:

>In the DSum statement below, how do I change the 'Yes'

>in 'Action1 = Yes'), to Is Not Null? I need to set the criteria to
>all records where the Action1 fields containg data.
> =DSum("Pagecount", "YourTable", "Action1 = Yes")

I'm not going to go into a spiel about how to write it in SQL, or
Coded IFs, or IIFs; just:

=DSum("Pagecount", "YourTable", "Action1 <> NULL")

which answers the question in a way the poster can understand
and yields the correct data without error in his/her application.

We've had reams of posts on the meaning ( & Idiosyncrasies of ) Null
which I'm sure the poster will be glad to look up in DejaNews.

So, Whatever gets you there.
Fun Isn't it :-) - No wonder the Newbies like me are confused.

Henry Craven
-------------------
H_Cr...@bigpond.com
--------------------------------------------------------------------------------

Vanderghast wrote in message <#tdsyK9j#GA.118@cpmsnbbsa02>...

>> H_Cr...@bigpond.com

>> Andrew Perrin wrote in message
><3723F89A...@mcmahon.qal.berkeley.edu>...
>> > I don't think so:
>> >
>> >?(null=null)
>> >Null
>> >
>> >?(null<>null)
>> >Null
>> >
>> >?(14556<>null)
>> >Null
>> >
>> >You need to use either Is Not Null (using SQL) or Not IsNull(Action1).
>> >
>> >ap
>> >
>> >Henry Craven wrote:
>> >
>> >> =DSum("Pagecount", "YourTable", "Action1 <> NULL")
>> >>
>> >> Henry Craven

>> >> H_Cr...@bigpond.com

>> >> David Morgan wrote in message <372358eb...@news.jps.net>...

[Sirron Kinet]

Write what you want.

ex: DLookUp("[FitADV]","Fit05ADV","[FitADV] Is Not Null")

Above worked for me!

Yes, it drove me crazy too! (see my message 4/25) just below yours.

David Morgan <d8...@hotmail.com> wrote in message
news:372358eb...@news.jps.net...

> = = = =
> = = = =
>

> In the DSum statement below, how do I change the 'Yes'
> (in 'Action1 = Yes'), to Is Not Null? I need to set the criteria to
> all records where the Action1 fields containg data.
>
> =DSum("Pagecount", "YourTable", "Action1 = Yes")
>
>

[David Morgan]

Thank you all.
I was able to accomplish my original goal by creating a separate query
for each Action. (Without pasting the QBE grid settings, here is the
SQL translation). BTW: if you know a quick way to copy the QBE grid
settings to the clipboard, please share.

SELECT LinkAll.CustodianID, [Last] & ", " & [First] AS CustodianName,
Count(BoxDetails.BoxID) AS ReviewBoxCount,
Sum(Nz([EndID])-Nz([BegID])+1) AS ReviewPageCount
FROM Custodians INNER JOIN (BoxDetails INNER JOIN LinkAll ON
BoxDetails.BoxID = LinkAll.BoxID) ON Custodians.CustodianID =
LinkAll.CustodianID
WHERE (((BoxDetails.ReviewBy) Like "*"))
GROUP BY LinkAll.CustodianID, [Last] & ", " & [First];

I join each of these separate queries in another query.
Maybe not the most optimized way to produce the result, but it does
work, and more importantly, I was able to figure it out on my own.
(Not that I don't appreciate the help of generous people like
yourself, but I love it when that happens!!)

Thanks again.

On Mon, 26 Apr 1999 23:26:39 +1000, "Henry Craven" <IUnk...@d.com>
wrote:

>I have no quibble with the fact that in Code, SQL Statements,The Criteria
>of the QBE Grid one uses "Is Not Null" and/or "Not IsNull()" et al.
>
>However.
>That does not belie the fact that using the Domain Function
>=DSum("Pagecount", "YourTable", "Action1 <> NULL")
>evaluates correctly.
>
>As a custom function, I presume the MS Access programmers
>decided to make it easy for the users by using more conventional
>( Excel ?) like syntax and handle things internally. - well and good.
>
>When David Morgan asks:

>>In the DSum statement below, how do I change the 'Yes'

>>in 'Action1 = Yes'), to Is Not Null? I need to set the criteria to
>>all records where the Action1 fields containg data.
>> =DSum("Pagecount", "YourTable", "Action1 = Yes")
>

>>> >> >In the DSum statement below, how do I change the 'Yes'
>>> >> >(in 'Action1 = Yes'), to Is Not Null? I need to set the criteria to
>>> >> >all records where the Action1 fields containg data.
>>> >> >
>>> >> > =DSum("Pagecount", "YourTable", "Action1 = Yes")
>
>

================================================

[Michel Walsh]

HI,

Select the SQL text, Ctrl-C. Ctrl-V will paste it.

Hope it may help,
Vanderghast, Access MVP.

David Morgan <d8...@hotmail.com> wrote in message
news:3725168b...@news.jps.net...

[Vanderghast]

Hi,

Just remembering that Null Is not equal to Null is the main point. Many
beginners think that NULL = NULL returns true, while it is not (except for
"eval").

Vanderghast, Access MVP.

Henry Craven <IUnk...@d.com> wrote in message

news:lPZU2.20312$MB3....@newsfeeds.bigpond.com...

[Henry Craven]

Very True.

Henry Craven
-------------------
H_Cr...@bigpond.com
--------------------------------------------------------------------------------

Vanderghast wrote in message <#Z9VMnJk#GA.52@cpmsnbbsa03>...

[John Viescas]

Just FYI, Access 2000 introduces another exception. In an ADO filter you
*cannot* say "IS NULL" or "IS NOT NULL". You have to say "= NULL" or "<>
NULL".

--
John Viescas
author, "Running Microsoft Access 2000"
http://www.amazon.com/exec/obidos/ASIN/1572319348

Vanderghast <Vande...@email.msn.com> wrote in message
news:#Z9VMnJk#GA.52@cpmsnbbsa03...

[Dev Ashish]

or having to include a custom fNull function in ASP to change those darned
strings. :-)

-- Dev

Henry Craven <IUnk...@d.com> wrote in message

news:KjNV2.21782$MB3....@newsfeeds.bigpond.com...
: Thanks John, Good to know.
: What's that old Saying...
: "There's nothing like consistency" ?
:
: Henry Craven
: -------------------
: H_Cr...@bigpond.com
: --------------------------------------------------------------------------
------
:
: John Viescas wrote in message ...
: >Just FYI, Access 2000 introduces another exception. In an ADO filter you

: >>
: >
: >
: >
:
:

[Henry Craven]

Thanks John, Good to know.
What's that old Saying...
"There's nothing like consistency" ?

Henry Craven
-------------------
H_Cr...@bigpond.com
--------------------------------------------------------------------------------