How do I build a countif expression in Access

  • Thread starter Thread starter Angela
  • Start date Start date
A

Angela

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
 
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.
Click to expand...

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))
 
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")
 
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.
 
Back
Top