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?

Thank you!

Jason

Hi Jason,

(IMG:style_emoticons/default/welcome2UA.gif)

What version of Access are you using? Knowing the Access version may help us determine a more appropriate response to your question.

Maybe you could try a different approach. For example:

...
WHERE Reqs.[Job Added]<=[Date].[DateKey] And Reqs.[Job Added]>#10/20/2010# AND ((Reqs.[Open/Closed]=1 AND (Reqs.ClosedDate >=#10/20/2010# OR Reqs.ClosedDate Is Null)) OR (Reqs.ClosedDate>=[Date].[DateKey]))
...

(untested)
Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

I'll play around with that. Thanks.

I'm on Access 2010

That worked! Thank you! I should have posted yesterday instead of spending a good chunk of the afternoon searching the web for a solution and coming up with nothing. Thanks again!

Hi,


(IMG:style_emoticons/default/yw.gif)

Glad to hear you got it to work. Good luck with your project.