How do I build a countif expression in Access

I want to build a countif expression in a query. I basically want to count
off another query but only if a field in another table is null.

COUNT(*) count all rows,

COUNT(fieldName) count all rows where the field value is NOT null,

COUNT(*) - COUNT(fieldName) so count the number of NULL under the said
column.


SUM( iif( someCondition, 1, 0 ) ) will count the number of records where
someCondition evaluates to true. Someone could also use:
-SUM(someCondition) in Jet, since false = 0 and a result evaluated to
true = -1.



Vanderghast, Access MVP



"Angela" wrote in message
...
I want to build a countif expression in a query. I basically want to count
off another query but only if a field in another table is null.

On Tue, 8 Jul 2008 10:53:00 -0700, Angela wrote:

I want to build a countif expression in a query. I basically want to count
off another query but only if a field in another table is null.

It's not clear to me where the field value comes from but, here is a
generic method to count nulls.

=Sum(IIf(IsNull([SomeField]),1,0))
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

The equivalent of the Excel CountIf is the DCount in VBA or the Count in a
query.
To use the Count, you need to create a Totals query, use the Count for the
field ou want to count on and use Is Null in the Criteria.

If you are wanting to return one value for that one field, depending on
where you are doing it, a DCount function may be a better choice. It is in
the form
=DCount("*","TableOrQueryName","[FieldName] Is Null")
--
Dave Hargis, Microsoft Access MVP


"Angela" wrote:

I want to build a countif expression in a query. I basically want to count
off another query but only if a field in another table is null.

Add this line of code to an empty field in your query

CountNulls: IIf([TheNameOfYourField] Is Not Null,0,1)

Then right click on the field and click totals. This will add a Totals line
that you will need to change to Sum. This will work if CountNulls is the
only field in your query but if there are other fields in this query and
their totals are set to Group By then the sum will be off. If you could let
us know more about your table design we would make sure we give you the right
answer.
--
Please remember to mark this as answered if this solves your problem.


"Angela" wrote:

I want to build a countif expression in a query. I basically want to count
off another query but only if a field in another table is null.