DSum Function with Null Criteria

[JithuAccess]

Hello Guys,

This is my code

=DSum("[strID]","[tblDaily Snap Shots SPU]","[datDate of Events]='IsNull'")

Here I want to display the total number of Records where the Field [datDate of Events] is null in a Text Box. I am getting #Error

Could you please let me know how to solve this

 

[June7]

=DSum("[strID]", "[tblDaily Snap Shots SPU]", "[datDate of Events] Is Null")

or

=DSum("[strID]", "[tblDaily Snap Shots SPU]", "IsNull([datDate of Events])")

But if you want to know how many records, need DCount, not DSum.

=DCount("*", "[tblDaily Snap Shots SPU]", "IsNull([datDate of Events])")

Why is field named 'strID' - ID field is text type?

However, testbox on form has no bearing on this code.

 

[plog]

1. DSum adds all your records values together. You want a count of your records. So you should be using DCount().

2. [strID] makes me think you are using a string as a primary key, that's usually a bad idea. Not totally wrong, but you usually want an autonumber as your primary key.

3. You aren't doing yourself any favors by using non-alphanumeric characters in names. I would eliminate your spaces in your table and field names. Makes coding like this that much more difficult.

4. You aren't testing for a Null value, but literally a string that is equal to the characters 'IsNull'. You should use the IsNull function around the field you want to test.

So you probably want:

=DCount("[strID]","[tblDaily Snap Shots SPU]","IsNull([datDate of Events])")

 

[JithuAccess]

=DSum("[strID]", "[tblDaily Snap Shots SPU]", "[datDate of Events] Is Null")

or

=DSum("[strID]", "[tblDaily Snap Shots SPU]", "IsNull([datDate of Events])")

But if you want to know how many records, need DCount, not DSum.

=DCount("*", "[tblDaily Snap Shots SPU]", "IsNull([datDate of Events])")

Why is field named 'strID' - ID field is text type?

However, testbox on form has no bearing on this code.

Thanks a lot. It worked perfect. Now how do we find the count of Not Null Values? I tried

=DCount("[strID]","[tblDaily Snap Shots SPU]","IsNull(0)[datDate of Events])")

Thanks

 

[theDBguy]

Now how do we find the count of Not Null Values?

Hi. Basically, the IsNull() part becomes Not IsNull().

 

[The_Doc_Man]

You could use "NOT IsNull..." or "NOT x IS NULL"

 

[JithuAccess]

Hi. Basically, the IsNull() part becomes Not IsNull().

Thanks a lot. I tried this code

=DCount("[strID]","[tblDaily Snap Shots SPU]","Not Is Null([datDate of Events])")

But getting #Error"

 

[JithuAccess]

Thanks a lot. I tried this code

=DCount("[strID]","[tblDaily Snap Shots SPU]","Not Is Null([datDate of Events])")

But getting #Error"

It worked

=DCount("[strID]","[tblDaily Snap Shots SPU]","Not IsNull([datDate of Events])")

Thanks

 

[JithuAccess]

You could use "NOT IsNull..." or "NOT x IS NULL"

Thanks a lot. It worked perfect

 

[theDBguy]

It worked

=DCount("[strID]","[tblDaily Snap Shots SPU]","Not IsNull([datDate of Events])")

Thanks

Hi. Glad to hear you got it sorted out. Cheers!

 

[The_Doc_Man]

Glad to have helped. Looks like theDBguy and I hit the same idea at the same time.

 

[June7]

Or
=DCount("*", "[tblDaily Snap Shots SPU]", "[datDate of Events] LIKE '*'")

All assume empty string is not allowed in field. I always set text field in table to not allow empty string.