Query not filtering by date

[dwheatley]

Hi,

I have a query with the following SQL:

SELECT tblCompanies.Company, tblServicePrice.Service, tblServicePrice.Price, tblServicePrice.Currency, tblServicePrice.[Number of Issues], tblDeadlines.Deadline
FROM (tblCompanies INNER JOIN tblDeadlines ON tblCompanies.ID = tblDeadlines.Company) INNER JOIN tblServicePrice ON tblCompanies.ID = tblServicePrice.Company
WHERE (((tblCompanies.Company)=[Forms]![SearchF]![Company]) AND ((tblServicePrice.Service)=[Forms]![SearchF]![Service]) AND ((tblDeadlines.Deadline)>Date())) OR ((([Forms]![SearchF]![Company]) Is Null) AND (([Forms]![SearchF]![Service]) Is Null) AND ((tblDeadlines.Deadline)>Date()));



The only aspect which is not working is the filtering by date. I have tried to make the query only return results after today's date. It seems that Access is simply ignoring my date command.

Please help!

[andy49]

Why not create
Qry1 results after date()

Then apply your query (minus date check) to qry1

I suspect the issue is your brackets. Mixing and with or often leads to issues if the brackets aren’t correct

[dwheatley]

I have now updated my SQL and have a new problem.

I can leave Company and Service fields blank and the Query will return those results filtered by date (great)
I can select both a Company AND a service, and the Query will return results filtered by date (also great)
However, if I leave EITHER Company or Service blank, then the Query will return no results whatsoever.

Do you know why this might be?

My SQL for the Query is:

SELECT tblCompanies.Company, tblServicePrice.Service, tblServicePrice.Price, tblServicePrice.Currency, tblServicePrice.[Number of Issues], tblServicePrice.Comments, tblDeadlines.DeadlineFROM (tblCompanies INNER JOIN tblServicePrice ON tblCompanies.ID = tblServicePrice.Company) INNER JOIN tblDeadlines ON tblCompanies.ID = tblDeadlines.Company
WHERE (((tblServicePrice.Service)=[Forms]![SearchF]![Service]) AND ((tblDeadlines.Deadline)>Date()) AND ((tblServicePrice.Company)=[Forms]![SearchF]![Company])) OR (((tblDeadlines.Deadline)>Date()) AND (([Forms]![SearchF]![Service]) Is Null) AND (([Forms]![SearchF]![Company]) Is Null));

[Bulzie]

Probably because you have AND for both those fields which works if both are null or has values. But if you have one null and other a value, then the AND returns no records as you would need an OR there I believe. You might need 2 more conditions each for Service and Company like:

((tblServicePrice.Service)=[Forms]![SearchF]![Service]) AND (tblDeadlines.Deadline)>Date()) OR ((tblServicePrice.Company)=[Forms]![SearchF]![Company]) AND (tblDeadlines.Deadline)>Date())

brackets might not be right

[dwheatley]

That has solved it, thanks a lot!