Handling Dates

[isxaa1]

I have two tables, Flats (FlatID, Court_No, Flat_No, Description) and Bookings (BookingID, FlatID, CheckInDate, CheckOutDate). The Flats table contain 19 flats that a company has and the Bookings table stores the bookings for a customer. What I would like to do is to devise a query that will return all the available flats. For example, I have a booking for 16/03/2003-20/03/2003 for Flat1. If I search for an available apartment between any of these dates, it should not return this flat (From a form that will have 2 input boxes for Check In Date and Check Out Date).

Can anyone help me please because I am really stuck.
Thanks

 

[Rich]

Search here for Bookings and Hotels this question has been answered before

 

[jfgambit]

Is there a record for each date reserved for Flat1...

16/03/2003 Flat1
17/03/2003 Flat1, etc...

Is so, why not build a query with all your information and in the criteria of the Date field add "Is Null". this would only show Rooms where no date is entered.

HTH

 

[isxaa1]

the relationship between Flats and Bookings is a many to one. A booking is associated with only one flat while a Flat is can be associated with more than one Booking.

P.S. There isn't a record for each date

 

[Jon K]

You can do it with two queries, as shown in the attached DB.

Open the form, enter a check in date and a check out date. Click on the Search button to view the available flats. The code in the On Click event of the Search button just runs the second query below:

qryFlatsBooked:-
SELECT Bookings.FlatID
FROM Bookings
WHERE Forms!Search!txtCheckInDate Between CheckInDate And CheckOutDate OR Forms!Search!txtCheckOutDate Between CheckInDate And CheckOutDate;

qryFlatsAvailable:-
SELECT Flats.FlatID, Flats.Court_No, Flats.Flat_No, Flats.Description
FROM qryFlatsBooked RIGHT JOIN Flats ON qryFlatsBooked.FlatID = Flats.FlatID
WHERE qryFlatsBooked.FlatID Is Null;

(When building qryFlatsAvailable in query Design view, double-click on the line that joins the FlatIDs and select the option that includes ALL records from 'Flats'. Access would add the correct JOIN in the SQL statement.)

Hope it helps.


The attached DB is in Access 97 format. If you use Access 2000 or 2002, choose Convert and save as a new name when the DB is opened for the first time.

 

[isxaa1]

Thanks Jon for your help but I have some bad news. Yours sample query doesn't really work. If you try searching for a flat that is booked from 16/03/2003-20/03/2003 for a CheckInDate on the 15/03/2003 and a CheckOutDate at 22/03/2003 returns that apartment as available, where it shouldn't .

 

[Jon K]

Yes, you are right. I have left out the case where the dates to be searched are outside of the booked ranges.


The following should fix it.

(1) Change the word "and" to "or" in the On Click event of the command button:-
--------------------------------------
Private Sub cmdSearch_Click()

' if nothing is entered in the two text boxes, exit.
If IsNull(Me.txtCheckInDate) or IsNull(Me.txtCheckOutDate) Then
Exit Sub
End If

DoCmd.OpenQuery "qryFlatsAvailable", acNormal, acReadOnly

End Sub
---------------------------------------

(2) Change the query qryFlatsBooked as follows:-

SELECT Bookings.FlatID
FROM Bookings
WHERE Forms!Search!txtCheckInDate Between CheckInDate And CheckOutDate OR Forms!Search!txtCheckOutDate Between CheckInDate And CheckOutDate
or CheckInDate between Forms!Search!txtCheckInDate and Forms!Search!txtCheckOutDate
or CheckOutDate between Forms!Search!txtCheckInDate and Forms!Search!txtCheckOutDate;

 

[isxaa1]

Thanks Jon. The staff works FINE!!!! You have been a great help.
Thank you very much

Tony