Results 1 to 12 of 12
  1. #1
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130

    Date Filter for Date/Time Field

    I have a query that I cannot get to return records.

    Code:
    SELECT WOTracking.Employee, Count(WOTracking.OrderNo) AS CountOfOrderNo, WOTracking.ContractCo
    FROM WOTracking
    WHERE WOTracking.Date_Time=Date()
    GROUP BY WOTracking.Employee, WOTracking.ContractCo;
    I want to use this to create a report showing the number of orders an employee logged for each ContractCo for today's date (it will be run at the end of each day). WOTracking.Date_Time is a combined date/time field.
    Right now it doesn't return anything.

    Any help would be greatly appreciate.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    There are a variety of ways. Here's one to try:

    WHERE WOTracking.Date_Time >= Date() And WOTracking.Date_Time < Date() + 1
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    Thank you. I used the first and it works perfectly.

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    The issue is the time component doesn't align with Date().

    The thing to understand about dates, is that they are stored as integers and time is a fractional component of one day.
    So try this:
    Code:
    SELECT WOTracking.Employee, Count(WOTracking.OrderNo) AS CountOfOrderNo, WOTracking.ContractCo
    FROM WOTracking
    WHERE (((WOTracking.Date_Time)>=Date() And (WOTracking.Date_Time)<Date()+1))
    GROUP BY WOTracking.Employee, WOTracking.ContractCo;
    EDIT: Once again, too slow in my testing and typing, and Paul beat me to the punch!

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by dccjr3927 View Post
    Thank you. I used the first and it works perfectly.
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by JoeM View Post
    too slow in my testing
    Testing?!? We don't need no stinking testing!!

    Edit: bonus points for naming the movie
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    Oh, is there a way to sum the CountofOrderNo for each employee?

    I don't know how you would sum them in the Employee Footer

  8. #8
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    Bonus Answer: The Treasure of the Sierra Madre

  9. #9
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    Nevermind. I got it. Thanks.

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by dccjr3927 View Post
    Bonus Answer: The Treasure of the Sierra Madre
    True but not the one I was thinking of, which was also a western...kind of. You get double bonus points for knowing the original movie. I'm low-brow, I was thinking Blazing Saddles.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Bonus Answer: The Treasure of the Sierra Madre
    Blazing Saddles too!

    EDIT: Once again, too slow! How is it that you always seem to post while I have the editor open? Are you monitoring me?

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by JoeM View Post
    Blazing Saddles too!

    EDIT: Once again, too slow! How is it that you always seem to post while I have the editor open? Are you monitoring me?
    LOL! No, just going fast because it's lunchtime. I'm off to make a sandwich now.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 12
    Last Post: 12-27-2018, 12:48 AM
  2. Replies: 5
    Last Post: 09-16-2018, 04:58 PM
  3. Replies: 6
    Last Post: 05-26-2017, 04:10 PM
  4. Replies: 6
    Last Post: 04-19-2016, 03:58 PM
  5. Subtracting a date/time field from a number to get date/time
    By Lifeseeker1019 in forum Programming
    Replies: 4
    Last Post: 03-28-2014, 12:59 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