Full Version: Or Is Null In Iif Statement
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
jrflanagan
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
theDBguy
Hi Jason,

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... 2cents.gif
jrflanagan
I'll play around with that. Thanks.

I'm on Access 2010
jrflanagan
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!
theDBguy
Hi,

QUOTE (jrflanagan @ Nov 29 2011, 08:33 AM) *
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!

yw.gif

Glad to hear you got it to work. Good luck with your project.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.