Results 1 to 7 of 7

Thread: Query with time

  1. #1
    Star Lounger
    Join Date
    Mar 2012
    Posts
    78
    Thanks
    20
    Thanked 0 Times in 0 Posts

    Query with time

    Hi

    I have a report that I want only transactions that were entered from 11:01 am to 10:29 am. The results of this time span could result in two days. The table has a field named "Date Entered" where the date is formatted as mm/dd/yyyy and the time is hh:mm:ss pm or am. I am looking for a criteria string that would give the results based on the time span above. I found the following criteria string:
    >=Date() AND <Date()+1 that gives me one day of transactions but not the next day before 11:01 am.

    Hope this is clear.

    Jean

  2. #2
    jwoods
    Guest
    WHERE DateEntered >= '1/1/2015 11:01:00' AND DateEntered <= '1/2/2015 22:29:00'

    See the DATEPART function on how to extract the specific values.
    Last edited by jwoods; 2015-12-15 at 19:14.

  3. #3
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,880
    Thanks
    153
    Thanked 177 Times in 170 Posts
    Just in case of confusion, I note that Jean specified 10:29 am, but jwoods coded it as 10:29 pm. Not sure who has the mistype

  4. #4
    Star Lounger
    Join Date
    Mar 2012
    Posts
    78
    Thanks
    20
    Thanked 0 Times in 0 Posts
    Hi

    My mistake it should be 10:59 am--how do I fix that? Is there a way to make the date option entered in by the user?

    Can I type this on the criteria line in a query?

    Jean
    Last edited by JeanM; 2015-12-16 at 08:11.

  5. #5
    jwoods
    Guest
    Quote Originally Posted by access-mdb View Post
    Just in case of confusion, I note that Jean specified 10:29 am, but jwoods coded it as 10:29 pm. Not sure who has the mistype
    I misread it...

    Change 22:29:00 to 10:29:00

  6. #6
    5 Star Lounger
    Join Date
    Jan 2010
    Location
    Fort McMurray, Alberta, Canada
    Posts
    669
    Thanks
    71
    Thanked 86 Times in 82 Posts
    In order to fully control the query and the datatypes involved, I'd recommend changing the suggested query slightly:

    WHERE DateEntered >= Cast('2015-01-01 11:01:00' As SmallDateTime) AND DateEntered <= Cast('2015-01-02 10:29:00' As SmallDateTime)
    I've stuck to strict Left To Right ordinality in the dates, and by using the Cast() function I don't depend on the query environment to perform an implicit type change.

  7. #7
    jwoods
    Guest
    No need to CAST anything.

    I would probably put the value of Date Entered into a variable declared as smalldatetime for the left-side date, and put the result of DATEADD to that Date Entered variable with "day" and "1" as the increment, into another variable declared as smalldatetime for the right-side date.

    HH and MM can be checked using DATEPART on both variables.

Posting Permissions

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