Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2005

    Unanswered: Query with a Date and a Time (Again)

    It seems like I had this issue before, but for the sake of me I cannot find my past email about how to correct.

    I have a form that displays 2 date/time fields -

    Start - 12/04/2010 08:00AM
    End - 12/05/2010 07:59 AM

    I created a query that adds the ship date and ship time into a combined general date format, then set criteria to query the records using the above fields -

    >=[Forms]![MainForm]![Start] And <=[Forms]![MainForm]![End]

    Problem is, the query is going beyond the times listed and pulling in all records for both dates. How can I make the query see the times and start at 8:00 am and cutoff at 7:59 am the next day?

    (there has to be a easier way!!!)

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    could we see the SQL for the query? | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    where adatecolumn betweeen [Forms]![MainForm]![Start] and [Forms]![MainForm]![End]
    bear in mind JET uses us date format and if you are comparing string literals you should encapsulate the date literal with the hash symbol
    bear in mind jet stores date AND time in the datevalue if you only specify the date potrion then it will retrieve records as if the time was 00:00:00 ie midnight, this is usually only a problem on the upper limit (the <= element)

    so your options may well be to expressly convert the value of the form to a date value

    where adatecolumn betweeen " & cdate([Forms]![MainForm]![Start]) & " and "& cdate([Forms]![MainForm]![End])
    where adatecolumn betweeen #" & format ([Forms]![MainForm]![Start],"mm/dd/yyyy") & "# and #"& formaat([Forms]![MainForm]![End],"mm/dd/yyyy") & "#"
    personally I #hate# the AM/PM designation its far clearer in my books to quote times according to the 24 clock (read theres less chance of a cock up)

    if you know 08:00 is the time band then you could add that to the query in background
    you could convert the string literals to date values, and then add (using the dateadd function)).
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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