How do I Check if Periods Overlap

[Cyd44]

I have been given a project to create a room booking dbase and wish to search current records to validate a booking before I save it.

My booking records contains info like:-
tblBooking
Date StartTime EndTime RoomNo
10/10/2011 9AM 1PM 7
10/10/2011 1PM 4PM 7
10/10/2011 9AM 2PM 1
10/10/2011 3PM 5PM 1
12/10/2011 5PM 6PM 1

frmBooking
If I make a booking for Room 1 for (say) 10/10/2011 for 2PM to 4PM (say) I need to look in the tblBooking to check that the room is free on this date and time.

Would anyone be able to advise on this please.

[ADezii]

In My Humble Opinion, this would be a perfect candidate for the MS Access Calendar App that is so popular here at Bytes. For any given Date, this Calendar will give you a visual cue as to whether or not a specific Room would be available for a specified Date and Time Interval. It is too involved to explain exactly how this is done, but I have created a simple Demo for you if you are interested.

1. Download the Attachment.
2. Click on any specific Date to expand a Listbox at the bottom of the Form that will display additional, expanded information. The Data is sorted by Date, Room Number, and Start Time and should give a clear indication as to whether or not a Reservation can occur.
3. Month/Year Navigation can easily be accomplished via several Controls.
4. You can Populate tblBookings, then have the Reservation display on the Form.
5. You can even add functionality that will allow you to add Reservations through the Calendar GUI directly.
6. Any questions, feel free to ask.

[Cyd44]

Thanks ADZel (again)

I am updateing records to Outlook and have started some logic to check for any overlap as follows:-

Dim Track As String

Track = "SELECT [BookLocation],[BookStartDate],[Bootime]FROM tblRoomBookings"
        WHERE
        "[BookLocation] = Me!BookLocation" AND "[BookStartDate] = Me!BookStartDate" AND "[BookTime] BETWEEN 'Me!BookTime' AND Me!BookEndTime"
        
If Not IsNull(Track) Then GoTo Room_Booked
Else
  GoTo Not_Room_Booked
End If

I cannot get the SQL to compile as it is giving me an "expected line number or label or end of statment" error and highlights from the WHERE statement. I have obviously made a syntax error but dont know which?

[ADezii]

As I see it, there are 4 conditions that you must check for regarding a New Reservation, namely: Date, Room Number, Start Time, End Time. If the Date is the same, AND the Room Number is the same, AND the Start Time is between the Start and End Time Range, OR the End Time is between the Start and End Time Range for any Record in the Database, then the Reservation is NOT Valid. The following Reservation will fail because of the 3:00 P.M. Start Time for Room #7 on 10/10/2011. An existing Reservation already exists for this Date (10/10/2011) and Room (7) from the Time Interval from 1:00 P.M. to 4:00 P.M., a 1 Hour Overlap. When you execute this Code, you will get a Reservation NOT Valid, but if you change the Start Time to 5:00 P.M., all is well in the world.

Dim strSQL As String
Dim dteDate As Date
Dim intRoomNum As Integer
Dim dteStartTime As Date
Dim dteEndTime As Date

‘Will FAIL
dteDate = #10/10/2011#
intRoomNum = 7
dteStartTime = #3:00:00 PM#
dteEndTime = #7:00:00 PM#

‘Will SUCCEED
'dteDate = #10/10/2011#
'intRoomNum = 7
'dteStartTime = #5:00:00 PM#
'dteEndTime = #7:00:00 PM#

strSQL = "[Date] = #" & dteDate & "# AND [RoomNo] = " & intRoomNum & " AND # " & _
         dteStartTime & "# BETWEEN [Start Time] AND [End Time] OR #" & _
         dteEndTime & "# BETWEEN [Start Time] AND [End Time]"
         
MsgBox IIf(DCount("*", "tblBookings", strSQL) > 0, "Reservation NOT Valid", "Reservation Valid")

[NeoPa]

Please check out Time Interval Overlapping (MS Access 2003). It's not the whole solution, but the understanding found here gives you a solid basis to progress from.

[Cyd44]

Thanks ADzeii

I know my logic was wrong but was concerned in the syntax as this was giving an error. Being new to SQL I am not confident writing statements as yet,

I have an input form which caputes Room Date Start and End Time for an appointment and want to use the Input form data to check that it is valid based upon the Room,Date & Start/End Times. Hence the Select Statement to compare the fields.

Appreciate your help as always

[Cyd44]

Hi Again,

Have tried you code after substituting my variables and am getting the following error when it runs:-

Run Time Error 3075
Syntax error in Expression
[BookStartDate]=20/10/2011 AND (BookLocation]=Room 2
AND #09:00:00# BETWEEN [BookTIME] AND [BookEndTime]
OR #13:00:00# BETWEEN [BookTime] AND [BookEndTime]

On Debug, the error highlights the MsgBox line

MsgBox IIF(DCount("*", "tblBookings", strSQL) > 0, "Your Messages"

Looks like it has almost worked?

[ADezii]

@Cyd44:
Taking into consideration your actual Field Names, and the fact that the Room Number is actually a String:

Dim strSQL As String
Dim dteDate As Date
Dim strRoomNo As String
Dim dteStartTime As Date
Dim dteEndTime As Date

dteDate = #10/10/2011#
strRoomNo = "Room 7"
dteStartTime = #5:00:00 PM#
dteEndTime = #7:00:00 PM#

strSQL = "[BookStartDate] = #" & dteDate & "# AND [BookLocation] = '" & strRoomNo & "' AND # " & _
         dteStartTime & "# BETWEEN [BookTime] AND [BookEndTime] OR #" & _
         dteEndTime & "# BETWEEN [BookTime] AND [BookEndTime]"
         
MsgBox IIf(DCount("*", "tblBookings", strSQL) > 0, "Reservation NOT Valid", "Reservation Valid")

P.S. - Your Table must be named tblBookings for the Code to work correctly, if not, modify Code Line #16.

[Cyd44]

Many thanks for that. The VBA works. However, there appears to be a logic error wich I will try and work out first. I have input the following:-

1. I have a booking record for 21/10/2011 (room 2)from 9am to 12pm and have tried to enter a record for 21/10/2011 (room 2)from 1pm to 4pm.
On checking the result comes back as Not Vailid.

My thoughts are that I will have to run 1 check to see if the start time is valid and another to see if the end time is vailid. The reason for this is to check first to see if start time conflicts with a record and then another to see if end time conflicts with a different record.

Am I right Here?
Let me play to see if I can work it out.

[Cyd44]

Have just MsgBox the strSQL to find that it is not showing the value of " & strRoomNo & ". Is this because we have declared it as a String? all other values of temp variables are being shown.?

[Cyd44]

Have sorted the strtRoomNo variable and this is OK. The script appears to be saying NOT Valid to all bookings except dates which are empty so it looks like it is either not comparing dates, room, time or it is not evaulating the variables.

[Cyd44]

Have entered a record for 21/10/2011 (room 2) for 6am - 8am and there is a booking starrting on this date for 9am. The script came back with VALID. It looks OK if we try a time before but some logic is wrong if we try after.

I also tried to book a room where there are no current bookings and it came back NOT Valid.

I will work on this tomorrow to see if I can understand what is happening?

[Cyd44]

I have been able to suss out what might be the problem. I got MsgBox to show me all variables on completion. It is not finding one of the fields in the table (this is BookEndTime).

This field is declared exacltly as BookTime and does contain data within the table yet the script finds everything except this one.

Cleary if the condition is testing this variable agianst a value it cannot do so and results might be illogical as a result I suspect.

Dim strSQL As String
Dim dteDate As Date
Dim strRoomNo As String
Dim dteStartTime As Date
Dim dteEndTime As Date

dteDate = Me!BookStartDate
strRoomNo = Me!BookLocation
dteStartTime = Me!BookTime
dteEndTime = Me!BookEndTime

strSQL = "BookStartDate = #" & dteDate & "# AND BookLocation = '" & strRoomNo & "' AND # " & _
dteStartTime & "# BETWEEN BookTime AND BookEndTime OR #" & _
dteEndTime & "# BETWEEN BookTime AND BookEndTime"

'MsgBox IIf(DCount("*", "tblRoomsBooking", strSQL) > 0, "Reservation NOT Valid", "Reservation Valid")
'MsgBox IIf(DCount("*", "tblRoomsBooking", strSQL) > 0, "Reservation NOT Valid", "Reservation Valid")
MsgBox (BookEndTime & strSQL)

End Sub

[NeoPa]

@Cyd
Did you see post #5? You didn't respond in any way.

If you look again you will see that your logic is fundamentally flawed as it doesn't reliably find overlaps (but only a subset of them).

[Cyd44]

Hi Neopa

Sorry I had not seen your answer but am grateful for your information. My most critical problem however is to get the script working properly as I have established that it is not retrieving info from one of the table fields. IE the BookEndTime field.

This is strange as the field is declared the same as the BookTime field which is a Date/Time field set as Medium Time. The script as shown above extracts all required fields from my table Except the BookEndTime field. Once I have got this problem resolved I can look at your logic advise in more detail.
Have you any thoughts on why I cannot retrieve the field in question?