COUNT BLANK FIELDS

[DaleFromEngland]

Hello folks...
Can anyone help me..

I have designed a query that will only show blank fields, so for instance, all telephone calls that have not been closed will show up as NULL, YEP... Right i have entered IS NULL in the criteria field, but when click on the total function in the query, the query dosent pull any records togeather. I know that you could do it by adding another field to the query, but i REALLLLLLLLYYYYYYY dont want to do this..

Thanks in advance...

Dale

 

[ian_ok]

Dale

What do the users of your d/b enter into the field that you are searching for null values when a call is closed?

Ian

 

[The_Doc_Man]

I've run into this from time to time. If the text field is fixed length (as opposed to "memo"), it might be empty rather than null. In that case you might wish to use IsEmpty rather than IsNull.

Just a thought.

 

[joeyreyma]

i thought IsEmpty is used to determine if a variant variable is initialized or not? i've never used it in a field or control.

 

[joeyreyma]

i thought IsEmpty is used to determine if a variant variable is initialized or not? i've never used it in a field or control.

 

[Jack Cowley]

Are you trying to count the number of records that have blank fields? Did you try the DCount Function?

[This message has been edited by Jack Cowley (edited 07-18-2001).]

 

[Pat Hartman]

Null values have certain properties that you need to understand to use properly. The help files contain many entries under the "Null Values" listing including examples. To solve your immediate problem:

The following two queries will produce different results if YourField Can contain Nulls. The first will return 0, the second will return a count of the nulls.

Select Count(YourField) As CountOfValues
From YourTable Where YourField Is Null;

Select Count(*) As CountOfValues
From YourTable Where YourField Is Null;

In general, it is more efficient to use the second syntax - Count(*) rather than the first - Count(SomeField). Plus, you will not loose the null values unless you want to.

If you use the totals button in the QBE grid to build the count query, Access will choose the first syntax. Instead, you can type the field yourself as:
CountOfSomething:Count(*)
rather than just selecting a column and setting the totals line to count. If you type the expression yourself, you'll need to set the totals line to expression.

 

[Jack Cowley]

Pat -

I learn more from reading one of your replies than all the help files combined. Especially Access2k's help files!

Thank you!

Jack