NZ not returning 0 values

[imfromwisconsin]

Can someone please tell me what I'm doing wrong on this? I think it's
one of those days...

I have a distinct list [ReasonList].[Reason] that contains a list of
words found in [Reasons].[Reason]. I'm trying to show the entire list
from [ReasonList].[Reason] and the total found in [Reasons].[Reason]
for a specific date range and if the value is null, then to show a 0.

The query works great in finding the results when the value is not
null, but I can't get it to display the 0 for those that are null.

Thanks in advance for your help!

-Ryan



SELECT ReasonList.REASON AS Reason, NZ(Count([Reasons].[REASON]),0) AS
Total
FROM ReasonList LEFT JOIN Reasons ON ReasonList.REASON =
Reasons.REASON
GROUP BY ReasonList.REASON, Reasons.DATE
HAVING (((Reasons.DATE) Between [Enter From Date:] And [Enter To
Date:]));

 

[KenSheridan via AccessMonster.com]

You are trying to restrict the result on the basis of values in a column
(Date) on the right side of a left outer join. This doesn't work; in effect
it turns it into an inner join. What you could do is count the number of
rows in Reasons in a subquery correlated with the outer query on the Reason
columns:

PARAMETERS
[Enter From Date:] DATETIME,
[Enter To Date:] DATETIME;
SELECT Reason,
(SELECT COUNT(*)
FROM Reasons
WHERE Reasons.Reason = ReasonList.Reason
AND Reasons.Date BETWEEN
[Enter From Date:] And [Enter To Date:])
AS Total
FROM ReasonList;

The subquery will return zero if there are no rows in Reasons for a reason
and within the date range.

Note that it’s a good idea to declare date/time parameters to avoid the
possibility of their being misinterpreted as arithmetical expressions and
giving the wrong results if the parameter values are entered in short date
format.

Also using Date as a column name is inadvisable as it’s the name of a built
in function, and could cause confusion. If you do use it be sure to qualify
it with table name.

Ken Sheridan
Stafford, England

Can someone please tell me what I'm doing wrong on this? I think it's
one of those days...

I have a distinct list [ReasonList].[Reason] that contains a list of
words found in [Reasons].[Reason]. I'm trying to show the entire list
from [ReasonList].[Reason] and the total found in [Reasons].[Reason]
for a specific date range and if the value is null, then to show a 0.

The query works great in finding the results when the value is not
null, but I can't get it to display the 0 for those that are null.

Thanks in advance for your help!

-Ryan

SELECT ReasonList.REASON AS Reason, NZ(Count([Reasons].[REASON]),0) AS
Total
FROM ReasonList LEFT JOIN Reasons ON ReasonList.REASON =
Reasons.REASON
GROUP BY ReasonList.REASON, Reasons.DATE
HAVING (((Reasons.DATE) Between [Enter From Date:] And [Enter To
Date:]));

 

[imfromwisconsin]

This is perfect; thanks for clarifying the declarations for date/time
as well. I agree that I should change it t to clean up the table.

I really appreciate your help on this, Ken!

-Ryan