Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Criteria - Date with time

  1. #1
    Board Regular
    Join Date
    Mar 2015
    Posts
    111
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking Criteria - Date with time

    Hello all, im new to access. Ive been hunting for a way to filter by the criteria of
    Between date-1 at 07:00 am and date at 07:00am (Giving a 24 hour window)
    any help on how to put this in as a criteria would be greatly appreciated

  2. #2
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,598
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Criteria - Date with time

    Between date-1 at 07:00 am and date at 07:00am
    This is just an example, which could be for any time - or is it just that you want 7:00? You might want to look at the DateAdd function. I hesitate to elaborate given that uncertainty, plus you're not saying if this is for a query criteria field, form control, report control or what. Nor can I tell if "date" is to be the current date or some date you wish to input.
    Last edited by Micron; Dec 3rd, 2017 at 02:02 PM. Reason: added info
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

  3. #3
    Board Regular
    Join Date
    Mar 2015
    Posts
    111
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Criteria - Date with time

    Quote Originally Posted by Micron View Post
    This is just an example, which could be for any time - or is it just that you want 7:00? You might want to look at the DateAdd function. I hesitate to elaborate given that uncertainty, plus you're not saying if this is for a query criteria field, form control, report control or what. Nor can I tell if "date" is to be the current date or some date you wish to input.
    Sorry I should have explained myself better. It is for a query criteria field.
    It is just the set time of 7:00 which will never change. The date I want to be based on yesterdays date at 7:00am to todays date at 7:00am. I assumed it would be written similar to. date()-1 07:00 to date() 07:00
    Thankyou for you reply

  4. #4
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,598
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Criteria - Date with time

    try Between (Date-1 + #07:00#) AND (Date + #07:00#) I'm thinking the DateAdd function is not really required in this case. This should work if your Regional Settings is the same as mine for date/time (mm/dd/yyyy hh:mm:ss)
    Last edited by Micron; Dec 3rd, 2017 at 02:52 PM.

  5. #5
    Board Regular
    Join Date
    Mar 2015
    Posts
    111
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Criteria - Date with time

    Quote Originally Posted by Micron View Post
    try Between (Date-1 + #07:00#) AND (Date + #07:00#) I'm thinking the DateAdd function is not really required in this case. This should work if your Regional Settings is the same as mine for date/time (mm/dd/yyyy hh:mm:ss)
    Thats great thank you for your help. I will try it when I get to work and let you know.
    Many thanks for replying

  6. #6
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,598
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Criteria - Date with time

    You're welcome. If that doesn't work, post back but make sure you include what you typed, what happened and what was supposed to happen. Good luck!

  7. #7
    Board Regular
    Join Date
    Mar 2015
    Posts
    111
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Criteria - Date with time

    The only change it needed to work was () after the date and then it worked perfectly.
    Between (Date()-1+#07:00:00#) And (Date()+#07:00:00#)

    Thankyou for your help

  8. #8
    New Member
    Join Date
    Dec 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Criteria - Date with time

    hi all, my name is Arsen)) I am a new member here )) thanks for your post , nice to meet you all here, in this web site

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •