Compare booking dates/times

[jaydeebe]

I have been trying to figure out how to compare a booking entered on a a form by a user with all previous bookings. As long as they do not overlap I would like the record saved. If they do overlap the user will be informed and make another selection. At the moment I am trying to use CASE to evaluate the dates. I will add time once i get this to work. I thought this may work but the debug picks up the refernece to the table (bookings) and thinks it's a field. How or Can, I reference the table properly in a case statement? Should I be looking for a different way to achieve my goals? I have a table for bookings, employees and resources. Also I have a form with the ocx calander for date picking and a resource list and employee list.

Private Sub book_click()
Dim NStart As Date
Dim NEnd As Date
Dim Start As Date
Dim Finish As Date

NStart = Me![StartDate]
NEnd = Me![EndDate]
Start = [Bookings]![StartDate]
Finish = [Bookings]![EndDate]

Select Case NStart
Case Start To Finish
DoCmd.OpenForm "CASE1"
End Select
Select Case NEnd
Case Me![StartDate] To Me![EndDate]
DoCmd.OpenForm "CASE2"
Case Else ' Other values.
DoCmd.OpenForm "CASE3"
End Select
End Sub

Jaydeebe

 

[PHV]

Take a look at RecordSet

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jaydeebe]

Thanks for the pointer PHV. I've been experimenting with recordset since you replied. Currently this code runs fine but isn't doing what I expected. It keeps giving me the second msgbox "seek unsuccesssful" whatever dates I put in. Anyway, I will keep experimenting.

Private Sub Command2_Click()
Dim dbs As Database, tdf As TableDef
Dim rst As Recordset, idx As Index, idx2 As Index
Dim fldStartdate As Field

' Return reference to current database.
Set dbs = CurrentDb
' Return reference to bookings table.
Set tdf = dbs.TableDefs!Bookings
' Create new index.
Set idx = tdf.CreateIndex("StartDate")
Set idx2 = tdf.CreateIndex("Enddate")
' Create and append index fields.
'Set fldStartdate = idx.CreateField("StartDate", dbText)

'idx.Fields.Append fldStartdate
' Append Index object.
'tdf.Indexes.Append idx
' Open table-type Recordset object.
Set rst = dbs.OpenRecordset("Bookings")
' Set current index to new index.
rst.Index = idx.Name
' Specify record to find.
rst.Seek ">=", Me![StartDate]
If rst.NoMatch Then
rst.Seek "<=", Me![EndDate]
If rst.NoMatch Then
MsgBox "Seek Successful."
Else
MsgBox "Seek Unsuccessful."
End If
End If
rst.Close
Set dbs = Nothing
End Sub

Jaydeebe

 

[PHV]

If rst.NoMatch Then
this test is true if seek is unsuccessful
You may also consider the RecordSet.Find (or .FindFirst) method

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jaydeebe]

rst.Find Me![StartDate] >= [Bookings]![StartDate] And Me![StartDate] <= [Bookings]![EndDate]

I tried it with this line but it says method or data member not found.

Jaydeebe

 

[PHV]

You have to reference Microsoft ActiveX Data Object, explicitely dim rst as DAO.RecordSet and use .FindFirst method.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jaydeebe]

Thanks for your help PHV but I think i'm trying to run before I can walk. I am now working on a way of doing this that is simpler. I'm going to flag each resource to be IN or OUT using the dates. If todays date falls within the booking dates then the resource flag will be on. If not then the flag will be off. Doing it this way allows me a much simpler check to see if a resource is available or not.

Jaydeebe

 

[tinymind]

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!

 

[jaydeebe]

Many thanks Tiny. I shall take some time to work through your code. I still think my idea may work. If a resource is flagged, in or out, for the current day, I will only have to test if that resource enddate falls within the new booking.

Jaydeebe

 

[jaydeebe]

After much butchering and many headaches I think I am coming to an understanding of how this works and have modified the code a bit to make it easier. I know it doesnt quite work properly but i'm sure it won't be long now. Many thanks Tiny for your input.

Private Sub Command4_Click()
Dim booking_rst As Recordset
Dim db As Database
Dim sql_string As String
Dim Start_time As Date
Dim Finish_Time As Date

Set db = CurrentDb

sql_string = "SELECT tblbookings.* FROM tblbookings;"

Start_time = txtStart.Value
Finish_Time = txtEnd.Value

Set booking_rst = db.OpenRecordset(sql_string)

If booking_rst.RecordCount > 0 Then
booking_rst.MoveLast
booking_rst.MoveFirst
While Not booking_rst.EOF
If Finish_Time > booking_rst!StartDate And Finish_Time < booking_rst!EndDate Then
MsgBox ("score")
booking_rst.MoveLast
Else
If Start_time >= booking_rst!StartDate And Start_time < booking_rst!EndDate Then
MsgBox ("score2")
booking_rst.MoveLast
Else
If Start_time <= booking_rst!StartDate And Finish_Time >= booking_rst!EndDate Then
MsgBox ("score3")
booking_rst.MoveLast
Else
If Start_time >= booking_rst!StartDate And Finish_Time <= booking_rst!EndDate Then
MsgBox ("score4")
booking_rst.MoveLast
Else: MsgBox ("Score5")
End If
End If
End If
End If
booking_rst.MoveNext
Wend
booking_rst.Close
End If

db.Close

End Sub

Jaydeebe