how do I count "is null" return "pending" in access

[Todd]

I wish to run a select query where there are null values and the null values
are not counted. The query runs properly but the null values are not
counted. I have tried using expressions like the "nz" and "IIF" but keep
coming up with errors. Odds are it is my ignorance that is getting in the
way.

 

[Golfinray]

In the query criteria of the field with nulls put Is Not Null

 

[Klatuu]

Count(Nz([SomeField],0))

This changes a Null to a Zero before the count.
What you are experiencing is normal. Nulls are skipped in counts.

 

[Todd]

I am so sorry. What I meant to say is I wish to run a select query where
there are null values and they get counted and grouped, then preferably
instead of being blank the row says "Pending" then the "count of" field where
values are null. IE group "Accepted", "Cancelled", "null" and count. While
Using Design view and putting the expressions in the criteria spot I received
error messages about the criteria. operator/operand errors dot errors
parentheses errors etc. I tried every way but I am missing something.

 

[Todd]

Where do I put "Count(Nz([SomeField],0))"? When I try to put it into design
view "total:" row under the field I am trying to count it gives the error
that it is not in list. I am quessing that it means it is not one of the
values in the drop down list box for the total row IE max min count group by
etc. When I remove "count" from the total row and place the expression on
the criteria line I get a "data type mismatch" error.

Count(Nz([SomeField],0))

This changes a Null to a Zero before the count.
What you are experiencing is normal. Nulls are skipped in counts.
--
Dave Hargis, Microsoft Access MVP

I wish to run a select query where there are null values and the null values
are not counted. The query runs properly but the null values are not
counted. I have tried using expressions like the "nz" and "IIF" but keep
coming up with errors. Odds are it is my ignorance that is getting in the
way.

 

[John Spencer]

Try setting the field as a calculated field and use that calculated field.

Field: TheStatus: NZ(Status,"Pending")




'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Todd]

I started a new thread with more information Thank you very much for your
time.

 

[Todd]

Okay I think "data type mismatch" comes from a where clause. I have made a
new thread with the sql statement and a better description. Thank you very
much for your time.

Count(Nz([SomeField],0))

This changes a Null to a Zero before the count.
What you are experiencing is normal. Nulls are skipped in counts.
--
Dave Hargis, Microsoft Access MVP

I wish to run a select query where there are null values and the null values
are not counted. The query runs properly but the null values are not
counted. I have tried using expressions like the "nz" and "IIF" but keep
coming up with errors. Odds are it is my ignorance that is getting in the
way.

 

[Todd]

I started a new thread with more information. If I understand you correctly
add the field to the design view again then try to place the expression
again? I tried this and still received an error message. Thanks for your
time. If you could look at the new thread and have any ideas please let me
know.

 

[Todd]

If you can decipher the query below it will show what I was trying to do.
This one worked. The key for me was learning about calculated fields and
where to put the expression in design view. Thank you for your input about
this.

SELECT IIf(IsNull([status]),"Pending",[Status]) AS [Status Grouped],
Count(IIf(IsNull([status]),"Pending",[Status])) AS [Status Count]
FROM [07517 m_eggers 1Q08]
WHERE ((([07517 m_eggers 1Q08].[Submitted Date Data Type]) Between
#4/13/2008# And #4/19/2008#))
GROUP BY IIf(IsNull([status]),"Pending",[Status])
ORDER BY IIf(IsNull([status]),"Pending",[Status]);

 

[Todd]

If you can decipher the query below it will show what I was trying to do.
This one worked. The key for me was learning about calculated fields and
where to put the expression in design view. Thank you for your input about
this.

SELECT IIf(IsNull([status]),"Pending",[Status]) AS [Status Grouped],
Count(IIf(IsNull([status]),"Pending",[Status])) AS [Status Count]
FROM [07517 m_eggers 1Q08]
WHERE ((([07517 m_eggers 1Q08].[Submitted Date Data Type]) Between
#4/13/2008# And #4/19/2008#))
GROUP BY IIf(IsNull([status]),"Pending",[Status])
ORDER BY IIf(IsNull([status]),"Pending",[Status]);

Count(Nz([SomeField],0))

This changes a Null to a Zero before the count.
What you are experiencing is normal. Nulls are skipped in counts.
--
Dave Hargis, Microsoft Access MVP

I wish to run a select query where there are null values and the null values
are not counted. The query runs properly but the null values are not
counted. I have tried using expressions like the "nz" and "IIF" but keep
coming up with errors. Odds are it is my ignorance that is getting in the
way.

 

[Todd]

If you can decipher the query below it will show what I was trying to do.
This one worked. The key for me was you telling me about calculated fields
and learning where to put the expression in design view. Thank you for your
input about this.

SELECT IIf(IsNull([status]),"Pending",[Status]) AS [Status Grouped],
Count(IIf(IsNull([status]),"Pending",[Status])) AS [Status Count]
FROM [07517 m_eggers 1Q08]
WHERE ((([07517 m_eggers 1Q08].[Submitted Date Data Type]) Between
#4/13/2008# And #4/19/2008#))
GROUP BY IIf(IsNull([status]),"Pending",[Status])
ORDER BY IIf(IsNull([status]),"Pending",[Status]);