Jaydeebe, There is no easy way around this - You may butcher this code as you see fit !!
I used this for a booking system. My requirement was to ensure that if any of the previous agreed appointments clashed flag it to the user.
I created four small module to test the start time and finish times of the bookings. Then a test to see if the start and finish time calshed with a long appointment. Then one that identified if there was a small appointment within the selected start time and finish time.
Tiny
'returns a true/false statement to see whether the booking times are clashing
'returns false if no booking collision
'returns true if a booking collision exists
Public Function check_booking_is_a_collision(this_room As String, this_date As Date, start_time As Date, Finish_Time As Date) As Boolean
Dim booking_rst As Recordset
Dim Pass_Flag As Boolean
Dim db As Database
Dim sql_string As String
Set db = CurrentDb
Pass_Flag = False
Debug.Print "Start_Time " & CStr(start_time)
Debug.Print "Finish_Time " & CStr(Finish_Time)
sql_string = ""
sql_string = "SELECT MASTER_Booking.MPI, MASTER_Booking.RoomID, MASTER_Booking.Date,"
sql_string = sql_string & " MASTER_Booking.TimeIn, MASTER_Booking.TimeOut, MASTER_Booking.Order "
sql_string = sql_string & "FROM MASTER_Booking WHERE (((MASTER_Booking.RoomID)='" & this_room & "')"
sql_string = sql_string & " AND ((MASTER_Booking.Date)=#" & Format(this_date, "mm/dd/yyyy") & "#));"
Set booking_rst = db.OpenRecordset(sql_string)
If booking_rst.RecordCount > 0 Then
booking_rst.MoveLast
booking_rst.MoveFirst
While Not booking_rst.EOF
'check whether the booking does not overlap the start time of another booking
'Debug.Print "-----"
Debug.Print check_start_time_collision(start_time, Finish_Time, booking_rst!TimeIn, booking_rst!TimeOut)
Debug.Print check_finish_time_collision(start_time, Finish_Time, booking_rst!TimeIn, booking_rst!TimeOut)
Debug.Print check_outer_time_collision(start_time, Finish_Time, booking_rst!TimeIn, booking_rst!TimeOut)
Debug.Print check_inner_time_collision(start_time, Finish_Time, booking_rst!TimeIn, booking_rst!TimeOut)
Debug.Print "-----"
If check_start_time_collision(start_time, Finish_Time, booking_rst!TimeIn, booking_rst!TimeOut) Then
Pass_Flag = True
booking_rst.MoveLast
Else
'check whether the booking does not overlap the finish time of another booking
If check_finish_time_collision(start_time, Finish_Time, booking_rst!TimeIn, booking_rst!TimeOut) Then
Pass_Flag = True
booking_rst.MoveLast
Else
'check whether the booking is not overlapping a booking already made
If check_outer_time_collision(start_time, Finish_Time, booking_rst!TimeIn, booking_rst!TimeOut) Then
Pass_Flag = True
booking_rst.MoveLast
Else
'check the booking is not being made within another booking time
If check_inner_time_collision(start_time, Finish_Time, booking_rst!TimeIn, booking_rst!TimeOut) Then
Pass_Flag = True
booking_rst.MoveLast
End If
End If
End If
End If
booking_rst.MoveNext
Wend
booking_rst.Close
End If
check_booking_is_a_collision = Pass_Flag
db.Close
End Function
'check whether a start time collision exists
'returns true if one exists
'returns false if not
Public Function check_start_time_collision(start_time1 As Date, finish_time1 As Date, start_time2 As Date, finish_time2 As Date) As Boolean
Dim start_collision As Boolean
start_collision = False
'test whether the start time falls with the booking time
Debug.Print DateDiff("n", finish_time2, start_time1)
If finish_time2 > start_time1 And finish_time2 < finish_time1 And DateDiff("n", finish_time2, start_time1) <> 0 Then
start_collision = True
End If
check_start_time_collision = start_collision
End Function
'check whether a finish time collision exists
'returns true if one exists
'returns false if not
Public Function check_finish_time_collision(start_time1 As Date, finish_time1 As Date, start_time2 As Date, finish_time2 As Date) As Boolean
Dim finish_collision As Boolean
finish_collision = False
'test whether the finish time falls with the booking time
If start_time2 >= start_time1 And start_time2 < finish_time1 Then
finish_collision = True
End If
check_finish_time_collision = finish_collision
End Function
'check whether an outer time collision exists
'returns true if one exists
'returns false if not
Public Function check_outer_time_collision(start_time1 As Date, finish_time1 As Date, start_time2 As Date, finish_time2 As Date) As Boolean
Dim outer_collision As Boolean
outer_collision = False
'test whether the start time falls with the booking time
If start_time2 <= start_time1 And finish_time2 >= finish_time1 Then
outer_collision = True
End If
check_outer_time_collision = outer_collision
End Function
'check whether an inner time collision exists
'returns true if one exists
'returns false if not
Public Function check_inner_time_collision(start_time1 As Date, finish_time1 As Date, start_time2 As Date, finish_time2 As Date) As Boolean
Dim inner_collision As Boolean
inner_collision = False
'test whether the start time falls with the booking time
If start_time2 >= start_time1 And finish_time2 <= finish_time1 Then
inner_collision = True
End If
check_inner_time_collision = inner_collision
End Function
Perfection is Everything
If it worked first time we wont be here!
