Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    bishmedia is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    44

    Adding a Date filter to SQL Query


    If i have the query below how do i add a date after the WHERE clause?
    eg. WHERE (((PickfaceCounts.Location) Is Null)); and PickfaceCounts.CountDate > 01/10/2018

    Code:
    SELECT PickfaceMaster.Pickface, PickfaceMaster.SelectPickface
    FROM PickfaceMaster LEFT JOIN PickfaceCounts ON PickfaceMaster.[Pickface] = PickfaceCounts.[Location]
    WHERE (((PickfaceCounts.Location) Is Null));
    Hope that made sense

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Something like:
    Code:
    WHERE PickfaceCounts.Location Is Null AND PickfaceCounts.CountDate > #01/10/2018#
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    bishmedia is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    44
    Damn it's coming up blank :-(

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Can you show us the whole SELECT statement that you used
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by bishmedia View Post
    Damn it's coming up blank :-(
    Are you sure that you have records that meet the criteria
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  6. #6
    bishmedia is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    44
    Sorry i meant to put the date as 01/08/2018 but it's still blank

    Code:
    SELECT PickfaceMaster.Pickface, PickfaceMaster.SelectPickface
    FROM PickfaceMaster LEFT JOIN PickfaceCounts ON PickfaceMaster.[Pickface] = PickfaceCounts.[Location]
    WHERE PickfaceCounts.Location Is Null AND PickfaceCounts.CountDate > #01/08/2018#;

  7. #7
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Are you sure that you have records that meet the criteria
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  8. #8
    bishmedia is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    44
    Still having issues with this
    Click image for larger version. 

Name:	Capture.JPG 
Views:	12 
Size:	21.2 KB 
ID:	35595
    Code:
    SELECT PickfaceMaster.Pickface, PickfaceMaster.SelectPickface
    FROM PickfaceMaster LEFT JOIN PickfaceCounts ON PickfaceMaster.[Pickface] = PickfaceCounts.[Location]
    WHERE (((PickfaceCounts.Location) Is Null) AND ((PickfaceCounts.CountDate)>#8/1/2018#));
    Basically,

    PickfaceCounts table holds all the pickfaces that have been counted
    PickfaceMaster table holds all the pickfaces that still need counting. (Over time this diminishes as the PickfacecCounts increases)

    Once all the pickfaces have been counted I want the PickfaceMaster to show all the locations again which is why I need the date option.

    I do hope that made sense : (

  9. #9
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Can you post a copy of your database with just a few records to illustrate the problem. Would be best to zip the db file
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  10. #10
    bishmedia is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    44
    The database is linked to an excel spreadsheet so drive/subfolder/filename would have to be setup for the excel file too, would that be ok?

  11. #11
    bishmedia is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    44
    Yes I do, all records start from 01/08/2018 but even if I put in a later date it comes up blank

  12. #12
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by bishmedia View Post
    The database is linked to an excel spreadsheet so drive/subfolder/filename would have to be setup for the excel file too, would that be ok?
    Yes. just post all the required files zipped
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  13. #13
    bishmedia is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    44

    Access file attached

    Inventory.zip
    Quote Originally Posted by Bob Fitz View Post
    Yes. just post all the required files zipped
    Hopefully the attached has worked and my previous explanation made sense

    Many thanks for your help

  14. #14
    bishmedia is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    44
    Also on the database attached I have removed the date and this just shows outstanding pickfaces where PickfaceCounts.Location= NULL

  15. #15
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Does something like the following work for you:
    Code:
    SELECT PickfaceCounts.Location, PickfaceCounts.CountDate
    FROM PickfaceCounts LEFT JOIN PickfaceMaster ON PickfaceCounts.[Location] = PickfaceMaster.[Pickface]
    WHERE (((PickfaceCounts.CountDate)>#9/1/2018#) AND ((PickfaceMaster.Pickface) Is Null));
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Adding query run date to a separate table
    By Maverick28 in forum Queries
    Replies: 2
    Last Post: 11-06-2017, 09:18 AM
  2. Adding in Date Range selector to query
    By tkbuc in forum Queries
    Replies: 1
    Last Post: 06-29-2017, 11:34 AM
  3. Adding a Calculated Date Field to a Query
    By MFriend in forum Access
    Replies: 8
    Last Post: 07-24-2015, 04:00 PM
  4. Query/Filter by Date
    By jasonbarnes in forum Reports
    Replies: 6
    Last Post: 01-04-2012, 04:05 PM
  5. Replies: 32
    Last Post: 06-20-2011, 07:30 PM

Tags for this Thread

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