Results 1 to 6 of 6
  1. #1
    robnsd is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2015
    Posts
    9

    query between two dates and times

    I have a table with fields that include date1 and time1 which hold a date (mm/dd/yy format) and time (short time.) I'm trying to use a query to bring up records for a date that the user selects that have a time between 7pm on the date the user selects and and 2:59am on the date that follows (an 8 hour period.) I've not been able to figure this out.

  2. #2
    InsuranceGuy is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Location
    Colorado
    Posts
    126
    In your query, you can use this approach

    Field: [date1] + [time1]
    Criteria: between ([Variable]+19/24) and ([Variable]+1+2/24+59/60*24)

    Edited to correct ranges...

    +19/24 adds 19 hours
    +1 adds 1 day
    +2/24 adds 2 hours
    +59/60/24 adds 59 minutes

    Cheers,

    Jeff

  3. #3
    robnsd is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2015
    Posts
    9
    Thank you Jeff. The variable is [Enter Date:] Below is what I entered in the criteria field for Field:[date1] + [time1]:

    Between ([Enter Date:]+19/24) And ([Enter Date:]+1+2/24+59/60*24)

    But i received the error message "The expression is typed incorrectly or it is too complex to be evaluated."

  4. #4
    InsuranceGuy is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Location
    Colorado
    Posts
    126
    Can you add [date1] and [time1] and come up with the correct values?

    If so, we might replace my math with the dateadd function:

    Between dateadd("h",19,[Enter Date:]) and dateadd("n",1619,[Enter Date:])

    Reads: From 19 hours after the date entered to 1619 minutes after the date entered

  5. #5
    robnsd is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2015
    Posts
    9
    Yes to your question. I used the dateadd function and it works only if the period does not overlap midnight. So it will work for 11am to 7pm but not for 7pm-3am.

  6. #6
    InsuranceGuy is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Location
    Colorado
    Posts
    126
    That should not be the case.

    You'll need to show your data set and the query's SQL for reference for further help.

    Jeff

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

Similar Threads

  1. Calculate Minutes Between Two Dates and Times
    By robrich22 in forum Programming
    Replies: 3
    Last Post: 01-31-2014, 07:56 PM
  2. Replies: 5
    Last Post: 09-11-2013, 03:42 PM
  3. Difference between Dates/Times
    By dr4ke in forum Queries
    Replies: 3
    Last Post: 06-26-2012, 06:30 AM
  4. checking availale dates and times
    By Nixx1401 in forum Forms
    Replies: 1
    Last Post: 01-04-2012, 01:54 PM
  5. Replies: 2
    Last Post: 12-07-2010, 08:27 AM

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