IIf statement in query to return 0

[frogmann]

Hello, I'm trying to create a simply query that will count the number of StudentIDs for a given month, and return 0 if it gets a null response for any of the months. In SQL view it looks as follows:
SELECT Month([DateReceived]) AS Mnth, Count(tblStudent.StudID) AS CountOfStudID
FROM tblStudent INNER JOIN tblStudentLessons ON tblStudent.StudID = tblStudentLessons.StudID
WHERE (((tblStudentLessons.LessonID)=248) AND ((tblStudentLessons.DateReceived) Between #1/1/2009# And #12/31/2009#))
GROUP BY Month([DateReceived]);

This works fine except that if, for any of the months there are no StudentLessons received, the query skips that line, which causes problems in my report. I've tried using an IIf statement, as follows:
SELECT Month([DateReceived]) AS Mnth, Count(IIf([tblStudent.StudID] Is Null,0,([tblStudent.StudID]))) AS StudIDCount
FROM tblStudent INNER JOIN tblStudentLessons ON tblStudent.StudID = tblStudentLessons.StudID
WHERE (((tblStudentLessons.LessonID)=248) AND ((tblStudentLessons.DateReceived) Between #1/1/2009# And #12/31/2009#))
GROUP BY Month([DateReceived]);

but this doesn't work either (I get the same result set with skipped lines for null months).
Any suggestions on this would be much appreciated. Thanks!

 

[PWise]

Create A table with year and month

Year ,month
2009,1
2009,2
2009,3
....

.....


2020,20


Select *
from monthyear
left join(SELECT Month([DateReceived]) AS Mnth,year([DateReceived]) AS yer,Count(tblStudent.StudID) AS CountOfStudID
FROM tblStudent INNER JOIN tblStudentLessons ON tblStudent.StudID = tblStudentLessons.StudID
WHERE (((tblStudentLessons.LessonID)=248) AND ((tblStudentLessons.DateReceived) Between #1/1/2009# And #12/31/2009#))
GROUP BY Month([DateReceived]))Stodents
on Students.mnth=monthyear.month
and Students.yer=monthyear.year

 

[Golom]

It's not that the months or the counts are NULL ... it's that they don't exist and therefore there is nothing to count.

Build a table called Intx with one numeric field Num and populate it with the numbers from 1 to 12. Then

SELECT Intx.Num AS Mnth, Nz(Count(*),0) AS StudIDCount

FROM (tblStudent As S INNER JOIN tblStudentLessons As L ON S.StudID = L.StudID)
     RIGHT JOIN Intx ON Intx.Num = Month(L.DateReceived) 

WHERE L.LessonID=248 
  AND Year(L.DateReceived) = 2009

GROUP BY Month(L.DateReceived)

ORDER BY Intx.Num

 

[frogmann]

Thanks for your responses. Golom I tried your answer and got an error on the join condition. You are trying to join an int with a date?

 

[lespaul]

a date is a float, the date portion is the integer and the time portion is the decimal. For instance today, Wednesday, December 9, 2009 at 7:50 am is:

40156.3263888889



Leslie

 

[frogmann]

Hello All,
Thanks to everyone for the responses. I tried a version of pwise's method and it worked, so case solved. Thanks much for all the help!