Results 1 to 5 of 5
  1. #1
    dwheatley is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    22

    Query not filtering by date

    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!

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,049
    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

  3. #3
    dwheatley is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    22
    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));

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,344
    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

  5. #5
    dwheatley is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    22
    That has solved it, thanks a lot!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 12-23-2014, 01:00 AM
  2. Filtering by date in a report?
    By aaron.irvine in forum Access
    Replies: 2
    Last Post: 10-29-2013, 07:09 PM
  3. Date Filtering
    By Amerlitong in forum Forms
    Replies: 6
    Last Post: 02-05-2013, 11:27 PM
  4. Filtering forms by date
    By rwest in forum Forms
    Replies: 1
    Last Post: 01-03-2012, 11:23 AM
  5. Date Filtering problem
    By TubbyGrey in forum Access
    Replies: 6
    Last Post: 10-22-2010, 10:46 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums