DCount Nulls

[patientgrow]

I am having trouble counting Nulls in a table using the DCount function.

Here is my code:
totalaccessions = DCount("*", "tblBDADailyWork", "Assigned is null ")

The field 'Assigned' has Nulls and it returns totalaccessions with 0. But
when I go into the table and key data into a null field and then delete it
and run this code it then counts the record I keyed and deleted as Null. Can
anyone help me out with what is going on here? And how can I just count the
blank fields?

Thanks!

 

[Jeff Boyce]

Define "blank"...

No, really! If you can't see something in the field, is it a space, a
blank, or a null. Access tends to compress out spaces, so if anything's
been entered into the field (and subsequently replaced with spaces or
deleted), Access treats that field differently than if nothing had ever been
entered (i.e., a Null).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Bryan in Bakersfield]

I have the same problem occasionally. In one text field blank data is null
but another it's an empty string even though there's no data in either column
in the file I import. It has something to do with the way the sata is
populated but I'm not sure why. Try looking for an empty string too.

totalaccessions = DCount("*", "tblBDADailyWork", "Assigned ='' or Assigned
is null")

Note the two single quotes ('') at the end of Assigned = ''

Bryan

 

[Klatuu]

Your code is correct to count rows where Assigned is null, so what you think
is Null probably is not. You can, however, count both Nulls and fields that
are empty strings using this:
totalaccessions = DCount("*", "tblBDADailyWork", "Nz([Assigned],"""")= """"")

Now just so you understand, if you use the DCount like this:
totalaccessions = DCount("Assigned", "tblBDADailyWork", "Assigned is null ")
It will always return 0 because when using a field name, DCount does not
count records where the field is Null, but the criteria does elminate them.

As an example, I ran the following on a table that has 140 records of which
3 have a Null value in the projcode field:

?dcount("*","dbo_project")
140
?dcount("projcode","dbo_project")
137
?dcount("*","dbo_project","projcode is null")
3
?dcount("projcode","dbo_project","projcode is null")
0

 

[patientgrow]

Bryan
Thank you. I tried your code and it picked up all the fields with blank data.