Results 1 to 3 of 3
  1. #1
    rplum is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Apr 2016
    Posts
    10

    Query specific times across days

    Hi All!

    I am trying to figure out if there is a way to query a certain set time across multiple days. Example we have three shifts (Days 7AM - 3PM, Afternoons 3PM - 11PM and Nights 11PM - 7AM) I want to pull the records for each shift and count the quantity of boxes received.



    Right now I have a form where you select your date to and date from. I also have three separate queries (days, afternoon nights). The problem is night shift goes from 11PM - 7AM the next day and when I run the query it pulls 0 records. I am just using a basic criteria for the shifts. It works for days and afternoons but not nights. example: day shift would be: >"07:00" And <"15:00" afternoons would be >"15:00" And <"23:00".

    Any idea if this is possible to query across multiple days but only a certain time frame?


    Thanks!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,791
    make a base query Q1, to split the Datefield into 2 fields: date0, Time0
    Date0: format([datefld],"mm/dd/yyyy"), Time0: format([datefld],"hh:mm")

    then Q2 , use Q1 to run your search on the date range and time ranges
    Date0 between #date1# and #date2" and time0 between #time1# and #time2#

  3. #3
    Welshgasman is offline Expert
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    616

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

Similar Threads

  1. Replies: 4
    Last Post: 11-21-2019, 08:56 AM
  2. Auto backup at specific times
    By mcmcd99 in forum Access
    Replies: 5
    Last Post: 12-07-2017, 03:56 PM
  3. Replies: 3
    Last Post: 11-01-2016, 03:46 PM
  4. Replies: 9
    Last Post: 03-07-2016, 04:17 PM
  5. query for times over differrent days
    By gaz100uk in forum Queries
    Replies: 3
    Last Post: 07-27-2011, 10:06 AM

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