Results 1 to 11 of 11

Thread: Comparing Dates

  1. #1
    Join Date
    Jun 2007
    Posts
    6

    Question Unanswered: Comparing Dates

    Hi,

    I am trying to make a booking system for a local Bed and Breakfast. I have two table - ROOMS and BOOKINGS.

    The ROOMS table holds all the information relavent to the room but
    the BOOKINGS table holds all the bookings made for that room.

    Now before a new booking can be made i want to see if the room is already booked or not.

    I am using an update query to do this. Which is not working apparently.

    I am accepting the date from the user through a CALANDER CONTROL and then checking if that date falls between any of the booking dates using the following code which dosent seem to work.


    UPDATE Rooms INNER JOIN Bookings ON Rooms.rid = Bookings.rid SET Rooms.rStatus = "OCCUPIED"
    WHERE ((([FORMS].[LookupRooms].[checkDate]) Between Format(([Bookings].[checkInDate]),"dd-mmm-yy") And Format(([Bookings].[checkOutDate]),"dd-mmm-yy")));



    can anybody plzzzzzzzzzzzzzzzz help

  2. #2
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    If your form control is formatted as date, and the fields in question are of the type Date/time, then there should be no need for formatting, I think

    ...WHERE [FORMS]![LookupRooms]![checkDate] Between [Bookings].[checkInDate] And [Bookings].[checkOutDate]

    Usually, when doing form referencing in queries, you'll use bang (!) not dot (.), thats for tables.
    Roy-Vidar

  3. #3
    Join Date
    Jun 2007
    Posts
    6

    Red face

    this is what my query looks like which does not update anything


    UPDATE Rooms INNER JOIN Bookings ON Rooms.rid = Bookings.rid SET Rooms.rStatus = "LUN"
    WHERE (((Format(([FORMS]![LookupRooms]![Calendar5]![Value]),"dd-mmm-yy")) Between Format(([Bookings].[expectedArrivalDt]),"dd-mmm-yy") And Format(([Bookings].[expectedDepartureDt]),"dd-mmm-yy")));



    ON THE OTHER HAND the query works fine if i enter the date manually rather then picking it up from a form as folllows:


    UPDATE Rooms INNER JOIN Bookings ON Rooms.rid = Bookings.rid SET Rooms.rStatus = "LUN"
    WHERE (((#7/2/2007#) Between Format(([Bookings].[expectedArrivalDt]),"dd-mmm-yy") And Format(([Bookings].[expectedDepartureDt]),"dd-mmm-yy")));

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    format is precisely that - a format. It is for display purposes and should not affect evaluation of the where clause. Did you try Roy's suggestion?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2007
    Posts
    6

    Red face

    yes i have tried roys sugestion - but have u noticed taht when i enter the date manually it automatically changes is to the US format ie mm/dd/yyyy

    I am using all UK based regional settings and all my fiels and entries are set to taht.

  6. #6
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Again - as long as all of these objects are Dates, there is no need to format. What formatting does, is casting them to string, which in some cases provides more "confusement", but in any case, wastes recourses.

    When working in the design view of a query, you will see a literal date in the format specified in your settings, but when switching to the SQL view, it will appear in US format (which is what I'm lead to believe is what Jet uses internally). In any case, a valid date can be compared with other valid dates without doing any formatting. It's only when you're building dynamic SQL strings (i e, when programming this in VBA), that you need to format, but then the most common formats are either US formst "mm\/dd\/yyyy" or ISO 8601 "yyyy-mm-dd".

    The reference [FORMS]![LookupRooms]![Calendar5]![Value] is wrong. If you're referring to the .Value property of the calendar control, remove it from the reference. If Value is a control on a subform, then you'll probably need the .Form keyword. I e either

    [FORMS]![LookupRooms]![Calendar5]

    or

    [FORMS]![LookupRooms]![Calendar5].Form![Value]

    For testing, create a new query without any tables, and stuff only the form reference into the field row

    MyTest: [FORMS]![LookupRooms]![Calendar5]

    When you get a reference that works (you get a one field/one column result showing the date from the form), you can use that exact reference in your where condition.
    Roy-Vidar

  7. #7
    Join Date
    Jun 2007
    Posts
    6

    Arrow have a look at the DB

    can u plz have a look at the access queries.

    u can download my db from

    http://www.box.net/shared/zm7vk3s9pf

    plz look at the test queries.

    i m trying to pick the dates from the calendar control called Calendar5 in the form LookupRooms

    thanking you in advance

  8. #8
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Now, going back to the initial question: "Now before a new booking can be made i want to see if the room is already booked or not."

    For this, you don't need an update query, you need a select. I entered your bookedByDt query and changed to

    SELECT *
    FROM Rooms INNER JOIN Bookings ON Rooms.[rid] = Bookings.[rid]
    WHERE [FORMS].[LookupRooms].[Calendar5] between Bookings.expectedArrivalDt AND Bookings.expectedDepartureDt

    If you set your calendar control to July 2nd, it should give one row.
    Roy-Vidar

  9. #9
    Join Date
    Jun 2007
    Posts
    6

    Smile it worked !!!

    Many many thanks RoyVidar -

    I have finally managed to make it work using the following query :

    UPDATE Rooms INNER JOIN Bookings ON Rooms.rid = Bookings.rid SET Rooms.rStatus = "Occupied"
    WHERE (((Format([FORMS].[LookupRooms].[Calendar5].[Value],"dd-mmm-yy")) Between Format([Bookings].[expectedArrivalDt],"dd-mmm-yy") And Format([Bookings].[expectedDepartureDt],"dd-mmm-yy")));


    this is working A OK !!!

    cheerz mate for all your effort

  10. #10
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Just curious...
    Isn't the room technically available on the departure date? The guest leaves the morning of the departure date, which would leave the room available that night.
    Inspiration Through Fermentation

  11. #11
    Join Date
    Jun 2007
    Posts
    6
    that's solved by using [departureDate]-1

Posting Permissions

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