By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,665 Members | 1,251 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,665 IT Pros & Developers. It's quick & easy.

Check on fields without a value (value Is Null)

P: n/a
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

Jan 30 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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.

--
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.
"Simon" <S.******@HCCnet.nl> wrote in message
news:43***********************@dreader25.news.xs4a ll.nl...

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?

Jan 30 '06 #2

P: n/a
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.

*** Sent via Developersdex http://www.developersdex.com ***
Jan 31 '06 #3

P: n/a
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.

--
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.

"steve.minnaar" <st*****@concise.com> wrote in message
news:8o*************@news.uswest.net...
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.

Jan 31 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.