Check on fields without a value (value Is Null)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Simon - 30 Jan 2006 07:34 GMT

Dear reader,

I found out a strange behaviour in a query of the type Total (summation
query).

In case of a normal select query with a criteria setting Is Null for
field-A, four (4) records are found.

If I specify in a Total query for the same field Count where Is Null the
result is zero (0) records.

Is that because field-A is a numeric field?

How can I check in a Total (summation) query on fields without a value for
numeric fields?

Thanks for any help.

Kind regards,

Simon van Beek

Reply to this Message

Allen Browne - 30 Jan 2006 07:50 GMT

Simon, in your Total query, what is in the Total row under this number field
with the Is Null criteria?

If it says Group By, you are applying the criteria *after* grouping (i.e.
Access puts it in the HAVING clause.) If the total shows a number (even
zero), then the *total* is not Null.

To apply the criteria *before* gouping, drag the field into the design grid
a 2nd time, and use Where in the Total row. Move the criteria here instead
of under the instance of the field where you have Group By. Access puts the
criteria in the WHERE clause, and gives the behavior you expect.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>
> I found out a strange behaviour in a query of the type Total (summation
[quoted text clipped - 10 lines]
> How can I check in a Total (summation) query on fields without a value for
> numeric fields?

Reply to this Message

steve.minnaar - 31 Jan 2006 10:57 GMT

The problem is that Access will not count Null fields. If you put the
criteria under the potentially Null field but count another field, say
an AutoNumber, which can never be Null then the query will work.

MS Access - A legend in it's own time.

Reply to this Message

Allen Browne - 31 Jan 2006 11:12 GMT

Yes, that's right. Any database worth using only counts the number of known
values.

The Null values (unknown or not applicable) are not counted, so the database
correctly reports the number of actual values you have in the field, and can
then go on to give correct averages etc.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> The problem is that Access will not count Null fields. If you put the
> criteria under the potentially Null field but count another field, say
> an AutoNumber, which can never be Null then the query will work.
>
> MS Access - A legend in it's own time.

Reply to this Message