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 online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    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
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858

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