I am having some trouble getting a query to count null values in the following statement.
SELECT Date.DateKey, Count(*) AS ActiveReqs
FROM Reqs, Date
WHERE (((Reqs.[Job Added])<=([Date].[DateKey]) And (Reqs.[Job Added])>#10/20/2010#) AND ((Reqs.ClosedDate)>=IIf([Reqs].[Open/Closed]=1,#10/20/2010#,[Date].[DateKey])))
GROUP BY Date.DateKey;
The IIF statement is where I'm getting hung up......If true, I would like it to count all ClosedDate values greater than or equal to 10/20/2010 including null values. If False, I would like it to count only ClosedDate values greater than or equal to the DateKey excluding nulls.
I have tried including "Or Is Null" after #10/20/2010# (in the true part of the IIF statement) but with no luck...the odd part is that if I include Or Is Null after [Date].[DateKey] (in the false part of the IIF statement) it does count the null values in the False part of the statement.
Any suggestions on how to get this query to work properly...and secondly why does the Or Is Null work in the false part of the statement above?