COUNT BLANK FIELDS (1 Viewer)

DaleFromEngland

Registered User.
Local time
Today, 09:53
Joined
Jul 16, 2001
Messages
15
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

Registered User.
Local time
Today, 09:53
Joined
May 1, 2001
Messages
90
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

Immoderate Moderator
Staff member
Local time
Today, 04:53
Joined
Feb 28, 2001
Messages
26,996
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

MSCEmbalmer
Local time
Today, 09:53
Joined
Jul 3, 2001
Messages
78
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

MSCEmbalmer
Local time
Today, 09:53
Joined
Jul 3, 2001
Messages
78
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

Registered User.
Local time
Today, 09:53
Joined
Aug 7, 2000
Messages
2,639
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

Super Moderator
Staff member
Local time
Today, 05:53
Joined
Feb 19, 2002
Messages
42,970
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

Registered User.
Local time
Today, 09:53
Joined
Aug 7, 2000
Messages
2,639
Pat -

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

Thank you!

Jack
 

Users who are viewing this thread

Top Bottom