Null Return on Count Function

[Scottie]

I have the following Query:

SELECT IIf(Count(*) Is Null,0,Count(*)) AS [No longer in Facility]
FROM Residents
GROUP BY Residents.[Last Status Date]
HAVING (((Residents.[Last Status Date]) Between [Forms]![Open_PIP
Report]![DateStart] And [Forms]![Open_PIP Report]![DateEnd]));

The query is still returning a null value. Many of the other queries in my
project require this to be at least a zero value to calculate properly. I
have tried using the NZ function also. Perhaps I am using it
incorrectly....any ideas?

 

[J_Goddard via AccessMonster.com]

Hi -

I don't quite understand your question. First, Count(*) never returns a Null,
only a number >= 0, so you don't need the IIF in the select.

What do you mean when you say "...the query returns a Null value.."? SELECT
queries don't return values as such, only recordsets. The recordsets may be
empty, or some fields in the records may contain Null.

What exactly is it that must be at least a zero?

Try this:

SELECT Count(*) AS [No longer in Facility]
FROM Residents
GROUP BY Residents.[Last Status Date]
HAVING (((Residents.[Last Status Date]) Between [Forms]![Open_PIP
Report]![DateStart] And [Forms]![Open_PIP Report]![DateEnd]));

But, if I read it correctly, this query will return a recordset containing
one field per record: the number of "Residents" records for each [last status
date] within a specified date range. Is that what you are looking for?

John

I have the following Query:

SELECT IIf(Count(*) Is Null,0,Count(*)) AS [No longer in Facility]
FROM Residents
GROUP BY Residents.[Last Status Date]
HAVING (((Residents.[Last Status Date]) Between [Forms]![Open_PIP
Report]![DateStart] And [Forms]![Open_PIP Report]![DateEnd]));

The query is still returning a null value. Many of the other queries in my
project require this to be at least a zero value to calculate properly. I
have tried using the NZ function also. Perhaps I am using it
incorrectly....any ideas?

 

[Scottie]

John,

I am sorry if I was vague, perhaps I should have been more clear. My query
is counting occurrences of dates between [Forms]![Open_PIP
Report]![DateStart] And [Forms]![Open_PIP Report]![DateEnd])) in the field
[Last Status Date]

I tried your version and it still is not returning >=0

Shouldn't this query return at least zero since it counts? Perhaps it would
be important to mention that there is no default value in this field. So
much of the data is null.

The query works fine so long as there is at least one record that falls w/in
the range(or >0). If not it is returning nothing....that is causing the
calculations on the report to fail.

Scottie

Hi -

I don't quite understand your question. First, Count(*) never returns a Null,
only a number >= 0, so you don't need the IIF in the select.

What do you mean when you say "...the query returns a Null value.."? SELECT
queries don't return values as such, only recordsets. The recordsets may be
empty, or some fields in the records may contain Null.

What exactly is it that must be at least a zero?

Try this:

SELECT Count(*) AS [No longer in Facility]
FROM Residents
GROUP BY Residents.[Last Status Date]
HAVING (((Residents.[Last Status Date]) Between [Forms]![Open_PIP
Report]![DateStart] And [Forms]![Open_PIP Report]![DateEnd]));

But, if I read it correctly, this query will return a recordset containing
one field per record: the number of "Residents" records for each [last status
date] within a specified date range. Is that what you are looking for?

John

I have the following Query:

SELECT IIf(Count(*) Is Null,0,Count(*)) AS [No longer in Facility]
FROM Residents
GROUP BY Residents.[Last Status Date]
HAVING (((Residents.[Last Status Date]) Between [Forms]![Open_PIP
Report]![DateStart] And [Forms]![Open_PIP Report]![DateEnd]));

The query is still returning a null value. Many of the other queries in my
project require this to be at least a zero value to calculate properly. I
have tried using the NZ function also. Perhaps I am using it
incorrectly....any ideas?

 

['69 Camaro]

Hi, Scottie.

The query is still returning a null value.

Yes. It will always return a NULL value, because you're grouping on a NULL
data set. There are no rows to group by Last Status Date, so there's no way
to count the members of each group. Hence, the NULL value.

I
have tried using the NZ function also.

But you tried to use it on the grouped NULL data set, instead of in the
other queries that need a count. Use the NZ( ) function in the other
queries when referencing the No longer in Facility column of the subquery.
For example:

SELECT CompanyName, COUNT(*) +
NZ((SELECT NoLongerInFacility
FROM qryResidentStatus), 0) AS Total
FROM Contacts
GROUP BY CompanyName
HAVING (CompanyName = 'De Lorean Motor Company')
OR (CompanyName = 'Barings Bank');

And a word to the wise: Don't use brackets on anything that could be used
in subqueries, because Jet is likely to choke on them, since Jet uses its
own internal syntax by placing brackets around subqueries. That means one
shouldn't try to fix a poorly named table, column, or query by placing
brackets around it in a query. Instead, never use illegal characters or
Reserved words when naming identifiers.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[J_Goddard via AccessMonster.com]

No - if the HAVING clause identifies no records, the query will return no
records. As I said, queries return recordsets, not values. The values you
want will be in fields in the returned records - if any.

Having said that, you may not need a query anyway. If all you are looking
for is the number of non-null dates in a range between two specified dates,
try the DCount function:

myCount=DCount("[last status date]","Residents","[last status date] Between
#" &
[Forms]![Open_PIP Report]![DateStart] & "# AND #" & [Forms]![Open_PIP Report]!
[DateEnd] & "#" )

It does not include nulls, so if there are no valid dates in the range, it
returns 0.

This might be better than a query.

John

John,

I am sorry if I was vague, perhaps I should have been more clear. My query
is counting occurrences of dates between [Forms]![Open_PIP
Report]![DateStart] And [Forms]![Open_PIP Report]![DateEnd])) in the field
[Last Status Date]

I tried your version and it still is not returning >=0

Shouldn't this query return at least zero since it counts? Perhaps it would
be important to mention that there is no default value in this field. So
much of the data is null.

The query works fine so long as there is at least one record that falls w/in
the range(or >0). If not it is returning nothing....that is causing the
calculations on the report to fail.

Scottie

Hi -

[quoted text clipped - 33 lines]