count number of dates seen or zero

[mdnuts]

Hi folks,

I'm having a moment - I just can't seem to get this one to work right. I've got the following query. I need to count the number of Null dates or show zero if there are no Null Dates.

SELECT DISTINCTROW qryNoticeResponseNew.fldNoticeID, Count(qryNoticeResponseNew.[fldResponseSeen]) AS fldCount
FROM qryNoticeResponseNew
GROUP BY qryNoticeResponseNew.fldNoticeID;

Which is just counting the number of dates so far. It got me to thinking I need to do something like this.

SELECT DISTINCTROW qryNoticeResponseNew.fldNoticeID, IIf(IsNull(qryNoticeResponseNew.[fldResponseSeen]),1,0) AS fldCount
FROM qryNoticeResponseNew
GROUP BY qryNoticeResponseNew.fldNoticeID;

Which pops a "cannot have aggregate function in expression" error.

Any ideas?

 

[plog]

So close. Put a SUM() around that field you built and you should get what you want.

Sum(IIf(IsNull(qryNoticeResponseNew.[fldResponseSeen]),1,0)) AS fldCount

The reason for the error you got is that if you have a GROUP BY clause, every field in the SELECT clause must be in the GROUP BY, or you must use an aggregate function (Sum(), Count(), Max(), etc.) on it.

 

[mdnuts]

Re: count number of dates seen or zero (Solved)

that did it! ahhh i knew better too.

Thanks!