Query Where Date "is null" not working

[mkremkow]

Hello all,

Access 2003 Windows PC:

I am trying to figure out why my Query isn't working. In this query, I want to get only those jobs that are still open and where at least one of four Yes/No fields is "Yes".

In my query, "Date Closed" field criteria "is null". My result is a listing of all jobs that have at least one of the four Yes/No fields, regardless of close date. Any suggestions?

Thanks!

[JKing]

Could you post your SQL for this query? This helps greatly in solving problems.

[abolos]

Hello all,

Access 2003 Windows PC:

I am trying to figure out why my Query isn't working. In this query, I want to get only those jobs that are still open and where at least one of four Yes/No fields is "Yes".

In my query, "Date Closed" field criteria "is null". My result is a listing of all jobs that have at least one of the four Yes/No fields, regardless of close date. Any suggestions?

Thanks!

You want something like:

SELECT tblNameAge.stName, tblNameAge.stAge, tblNameAge.Sex, tblNameAge.Married, tblNameAge.Divorced, tblNameAge.Single
FROM tblNameAge
WHERE (((tblNameAge.Divorced)=Yes)) OR (((tblNameAge.Single)=Yes));

[JKing]

Yes, that's what I'm looking for but post the SQL for the query you're having a problem with. The one you spoke of in your first post.

[mkremkow]

Okay - her is what I have:

SELECT [Job Log].[Date Closed], [Job Log].WebDueDate, [Job Log].ThePulse, [Job Log].Internet, [Job Log].Desktop, [Job Log].MedWeb, [Job Log].[Job ID], [Job Log].[Document Name], [Job Log].[Marketing Rep Name]
FROM [Job Log]
WHERE ((([Job Log].[Date Closed]) Is Null) AND (([Job Log].ThePulse)=Yes)) OR ((([Job Log].Internet)=(Yes))) OR ((([Job Log].Desktop)=(Yes))) OR ((([Job Log].MedWeb)=(Yes)))
ORDER BY [Job Log].WebDueDate;

Thanks!

[JKing]

Hi there, the problem is that the Null constraint on the date is only being placed in the first case. If you look at the where statement as having seperate cases split by the keyword OR then you will see that its groups DateClosed and ThePulse as one and then each of the other Yes/no fields as a seperate case or condition if you will.

So here's how you can fix it:

SELECT [Job Log].[Date Closed], [Job Log].WebDueDate, [Job Log].ThePulse, [Job Log].Internet, [Job Log].Desktop, [Job Log].MedWeb, [Job Log].[job ID], [Job Log].[Document Name], [Job Log].[Marketing Rep Name]
FROM [Job Log]
WHERE ((([Job Log].[Date Closed]) Is Null) AND (([Job Log].ThePulse)=Yes))
OR ((([Job Log].[Date Closed]) Is Null) AND (([Job Log].MedWeb)=Yes)) 
OR ((([Job Log].[Date Closed]) Is Null) AND (([Job Log].Desktop)=Yes)) 
OR ((([Job Log].[Date Closed]) Is Null) AND (([Job Log].Internet)=Yes));
 

[mkremkow]

That's it! Thank you - it works perfectly now!

[JKing]

You're very welcome!

Jking