locked
Writing query for access and comparing dates RRS feed

  • Question

  • User404418099 posted
    Bookings <tfoot></tfoot>
    Booking_ID Booking_FromDate Booking_ToDate Room_ID Booking_ByUser
    9 14/02/2011 16/02/2011 3 Admin
    10 18/03/2011 22/03/2011 2 Admin
    11 26/06/2011 02/07/2011 1 Admin
    12 02/07/2011 10/07/2011 4 Admin
    17 09/06/2011 14/06/2011 4 Admin
    18 09/06/2011 10/06/2011 4 Admin
    19 09/06/2011 10/06/2011 1 Admin

    For the above mentioned table I am executing this query: SELECT Room_ID, Format(Booking_ToDate,'dd/mm/yyyy') FROM Bookings WHERE Format(Booking_ToDate,'dd/mm/yyyy')>=Format(Date(),'dd/mm/yyyy')

    and the output i am getting is :

    Query1 <tfoot></tfoot>
    Room_ID Expr1001
    4 14/06/2011
    4 10/06/2011
    1 10/06/2011
    3 16/02/2011
    2 22/03/2011
    4 10/07/2011

    The value of Format(Date(),'dd/mm/yyyy') i am geting is 09/06/2011, still it is showing rows having date 16/2/2011 and 22/03/2011. Please guide me with query.

    -- Priyank

    N.B. the format for columns Booking_From and Booking_ToDate is already set to 'dd/mm/yyyy' format from the design view of table 'Bookings.'

    Thursday, June 9, 2011 10:59 AM

Answers

All replies

  • User-119530012 posted

    If you want to compare dates why don't you try this http://www.w3schools.com/sql/func_datediff.asp

    WHERE DATEDIFF(day,startdate,enddate) > 0
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 9, 2011 11:13 AM
  • User404418099 posted

    This doesn't work with MS Access

    Thursday, June 9, 2011 2:58 PM
  • User404418099 posted

    Thanks... it is working..... just the way we pass the parameter is lil diff for access.... for access it WHERE DATEDIFF('d'.startdate,enddate) > 0 :) :) thanks a lot marco :)

    Thursday, June 9, 2011 3:02 PM