Results 1 to 11 of 11
  1. #1
    kdestef1 is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    7

    BETWEEN Criteria for Start and End Dates that are looking at Dates with Times


    Hi Experts,

    Trying to figure out a Criteria to add to my query that will return data between two entered dates, which looks like this: Between [Start Date] and [End Date]. However, the problem with this is the data that has to be entered into the table for the Creation Instant field I'm searching on is a Date with time. So when I enter my standard dates in the popup Start and End Dates Parameter value, the query returns nothing because it's looking at dates with times (see attached snapshots). How can I write the criteria to only look at the dates and ignore the times? I thought maybe formatting my table and query dates/times to just a short date would work, but it does not since the data is stored as a date with time. Thinking I have to do this from the query Criteria side? Or do I need to split my times out from my date into it's own field?

    Thanks,
    kdestef1
    Attached Thumbnails Attached Thumbnails Snapshot1.JPG   Snapshot2.JPG  

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Are you sure that is the issue? I've dealt with something like this before, but it was about not getting records that were time stamped on the end date. That is because if you don't specify a time on that date, the cutoff is midnight, and that is because date values with no time default to 12:00:00 AM. If you get no records then I suggest testing by inputting times into your parameter prompt and see if that makes a difference. If it doesn't, there is something else wrong. Your pics don't prove that there is a time component to the data in your date fields either (at least values that are not just defaults).
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with micron. Do you have details on what you really expect in your EndDate values?

    Also, be aware that Access does not necessarily play well with fields that have embedded spaces in their names.
    I'm not sure where you are in your database development/testing, but I'd be removing the spaces in any field or object names.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I suspect you've correctly identified your own problem. Try

    Between [Start Date] And DateAdd("d",1,[End Date])
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not sure who that is directed to as I don't have such a problem. Regardless, I'll get out of the way now.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  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 Micron View Post
    Not sure who that is directed to as I don't have such a problem.

    If that is directed at me, my post was directed to kdestef1, who posted the problem.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    apr pillai's Avatar
    apr pillai is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2010
    Location
    Alappuzha, India
    Posts
    209
    I assume [Start Date] And [End Date] are parameter values and pure date values, without the time component. The Field Value recorded with Time value need to drop the Time part in order to compare it with Start Date and End Date Parameter.

    Int([Date FieldName]) Between [Start Date] AND [End Date] should work, I think.
    Last edited by apr pillai; 03-05-2021 at 08:12 PM.

  8. #8
    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 apr pillai View Post
    Int([Date FieldName] Between [Start Date] AND [End Date] should work, I think.
    That would work (if you fixed the parentheses) but is less efficient on larger datasets, since the Int() function would be applied to every record in the table. You'd lose the benefit of any indexing on the date field.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    kdestef1 is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    7
    Pbaldy thank you so much for your reply, your solution works perfectly!

  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 kdestef1 View Post
    Pbaldy thank you so much for your reply, your solution works perfectly!
    Happy to help!

    You'd want to tweak if there could be times right at midnight.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    apr pillai's Avatar
    apr pillai is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2010
    Location
    Alappuzha, India
    Posts
    209
    Quote Originally Posted by apr pillai View Post
    I assume [Start Date] And [End Date] are parameter values and pure date values, without the time component. The Field Value recorded with Time value need to drop the Time part in order to compare it with Start Date and End Date Parameter.

    Int([Date FieldName] Between [Start Date] AND [End Date] should work, I think.
    Int([Date FieldName]) Between [Start Date] AND [End Date]

    Parentheses fixed. Thank you pbaldy.

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

Similar Threads

  1. Replies: 5
    Last Post: 10-02-2017, 02:02 PM
  2. Replies: 2
    Last Post: 09-19-2017, 11:31 AM
  3. query between two dates and times
    By robnsd in forum Queries
    Replies: 5
    Last Post: 10-22-2015, 10:21 AM
  4. List Dates between Start and Finish Dates
    By Buddus in forum Queries
    Replies: 1
    Last Post: 07-26-2013, 01:58 PM
  5. Difference between Dates/Times
    By dr4ke in forum Queries
    Replies: 3
    Last Post: 06-26-2012, 06:30 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