DCOUNT problem (dont identify blank?)

[chris-uk-lad]

Hi, this might have got lost in the other topic so posting a fresh, I have a formula to work with where as i want it to return the number of records that dont have the following Status entries.

=DCount("[Ref_No]","New","[Status]not in ('A','B','C','D' )")

However, i want it to then be left with fields where [Status] is ' ' i.e empty, nothing inside. It doesnt seem to pick up on an empty cell though. So where i want it to return 2 empty status boxs, the result returned is 0.

Any idea why? and a resolution?

 

[gemma-the-husky]

design a query that tests for status is null or status = ""

then just do

dcount("*","myquery")


------
you may get the same thing with

dcount("*","mytable","status test goes here")

but i'm not exactly sure how to set out this criteria test in the where clause

 

[LPurvis]

The domain functions are effectively simplisticly deployed queries.
As Gemma rightly says - the crucial requirement is that you not Select from a specific field.
By definition - Counting a field's entries excludes Null records.
i.e.
SELECT COUNT(FieldName) FROM TableName WHERE FieldName Is Null
will always return no results for any table or field.
SELECT COUNT(*) FROM TableName WHERE FieldName Is Null
will return the count as you'd want.
Hence the equivalent in a domain function
DCOUNT("*", "TableName", "FieldName Is Null")
or if you have zero length strings in your data (they're nasty things to my mind - harming the efficiency of calls such as this)...
DCOUNT("*", "TableName", "FieldName Is Null OR FieldName = ''")

 

[chris-uk-lad]

so is it not possible to include NULL in "values that are not in A,B,C,D,E" ? It has to specifically be stated as a count all that are null?

 

[LPurvis]

Oh so you want to return (count) Null instances of the Status field?

=DCount("*","New","[Status] Not In ('A','B','C','D') OR [Status] Is Null")

Note that, were you looking for those results you couldn't just include Null as a value
=DCount("*","New","[Status] In (Null, 'A','B','C','D')")
as the In clause discounts Null.

 

[chris-uk-lad]

Worked a treat, thanks very much

 

[LPurvis]

Nae bother.