Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2008
    Posts
    3

    Unanswered: Access Query DateandTime by Time

    I'm pulling my hair out over a query I'm trying to write in Access 2000. I've searched and searched with nothing about my particular application.....

    I've got a database with a lot of data. Every record has a date and time stamp in the field "DateandTime" (creative, huh?). I am trying to do a query to show me every data entry within a certain time range for every day within a given date range. For example, I want it to return the values entered between the times of 8:00 AM and 9:30 AM for every day. I can't get it to do anything if I specify a time. Here's some code snippets and my results:

    Code:
    ...Where ( (([FloatTable].[DateandTime])>#1/1/2007#) AND  (([FloatTable].[DateandTime])<#1/10/2007#) )....
    This will return all the values between that date range correctly.

    Code:
    ...Where ( (([FloatTable].[DateandTime])>#1/1/2007#) AND  (([FloatTable].[DateandTime])<#1/10/2007#) AND  (([FloatTable].[DateandTime])<#9:30:00 AM#) AND  (([FloatTable].[DateandTime])>#8:00:00 AM#) )....
    This doesn't return anything, and I don't get any errors either.... I think it's automatically adding in 12/30/1899 to the time, and I don't have data that old.... I've tried putting the time in every way I can think of, with no luck. I've also experimented a little with TimeSerial, but that didn't help either.

    Thanks.

    Danny

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    WHERE DatePart("h", DateAndTime) BETWEEN 8 AND 10
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please (stop (with ((all)) the unnecessary) parentheses)
    Code:
    WHERE FloatTable.DateandTime > #2007-01-01#
      AND FloatTable.DateandTime < #2007-01-10#
      AND FORMAT(FloatTable.DateandTime,"HH:NN")
            BETWEEN '08:00' AND '09:30'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    Code:
    WHERE DatePart("h", DateAndTime) BETWEEN 8 AND 10
    gee, george, that would include all the times from 9:30 to 10:00, wouldn't it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    please (stop (with ((all)) the unnecessary) parentheses)
    (To (be (fair (it (is (Access () that) seems) to) think) they) help)

  6. #6
    Join Date
    Jan 2008
    Posts
    3
    Quote Originally Posted by r937
    please (stop (with ((all)) the unnecessary) parentheses)
    Code:
    WHERE FloatTable.DateandTime > #2007-01-01#
      AND FloatTable.DateandTime < #2007-01-10#
      AND FORMAT(FloatTable.DateandTime,"HH:NN")
            BETWEEN '08:00' AND '09:30'
    This worked great. Thanks.....

    Danny

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by r937
    gee, george, that would include all the times from 9:30 to 10:00, wouldn't it?
    I misssed the 9:30 part
    And I just bunged a couple of numbers in there for good measure...

    Apologies, it was a s*** day.
    George
    Home | Blog

Posting Permissions

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