One date while entering data, then check if it falls between all on a tbl/qry.

[MackMan]

Hi all. o/

Bit of a difficult one to explain (I think).


However... I have a theory on how I can get around this, but was wondering if anyone had any advice on a way that's easier.

I have a very simple Union Query which lists all entries in a couple of tables for meeting room bookings.

RoomID, FstBookDate, LstBookDate Type.
-------------------------------------------------
1 19/10/2016 21/10/2016 Booking
1 23/10/2016 25/10/2016 Booking
1 06/01/2017 09/01/2017 Maintenance
2 24/08/2016 27/08/2016 Booking

And so on...

I'm trying to come up with an way, that when a user starts a new booking and types a date in the FstBookdate (Date from) and using beforeupdate, have access look at the query and check to see each record, and cancel the entry should it fall within the list of dates.

For example, Meeting room 1 and user types in 24/10/2016 for the DateFrom, I need a msgbox and a cancel event as it's already booked on that day.

Am I right in thinking I'll need DAO with recordset and cycle each record in turn and return a true, if start date is greater than and less that the dates in each record?

Or is there an easier way?

Any advice as always is appreciated.

 

[arnelgp]

you only need to use dcount() function to check if there is already activity on that room for that particular date:

private sub dateFrom_BeforeUpdate(cancel as integer)
if nz( dcount("*", "yourUnionQuery", "[RoomID] = " & me.txtMeetingRoom & " And #" & Format(me.dateFrom,"mm/dd/yyyy") & "# Between [FstBookDate] And [LstBookDate]"), 0) > 0 then
' already booked
msgbox "Room " & me.txtMeetingRoom & " already booked on " & me.dateFrom & "."
cancel = True
End if
end sub

 

[sneuberg]

Checking the start date alone won't be enough. For example using the booking you posting the user could enter 18/10/2016 and that wouldn't be a conflict until he entered 19/10/2016 for an end date. Both the start and end dates have to be checked against the existing bookings to see if there are any overlaps. Overlapping dates can be determined by this function:

 'It is assumed that a0 <= a1 and b0 <= b1
Private Function IntervalsOverLap(a0 As Date, a1 As Date, b0 As Date, b1 As Date) As Boolean

IntervalsOverLap = a0 <= b1 And b0 <= a1

End Function

That looks too simple but it really works as long as the start dates are on or before the end dates. With that you can loop through the existing bookings using this function to check for conflicts. The following code does that and is demonstrated in the attached database. Note that you will have to change UnionQuery to the actual name of your query.

Private Function BookingsOverlap(StartDate As Date, EndDate As Date, RoomID As Long) As Boolean

Dim rst As DAO.Recordset
Dim strErrorMsg As String   '
Dim OverlapError As Boolean
Dim strSQL As String
If StartDate > EndDate Then
     BookingsOverlap = True
     MsgBox "Error: The start date cannot be after the end date"
     Exit Function
End If
strSQL = "SELECT * FROM [UnionQuery] WHERE [RoomID] = " & RoomID
Set rst = CurrentDb.OpenRecordset(strSQL)
OverlapError = False
Do While Not rst.EOF
    If IntervalsOverLap(rst!FstBookDate, rst!LstBookDate, StartDate, EndDate) Then
        strErrorMsg = strErrorMsg & "The Booking: " & StartDate & " - " & EndDate & ") you are trying to add" & vbCrLf & _
            " is already booked on: " & rst!RoomID & " (" & rst!FstBookDate & " - " & rst!LstBookDate & ")" & vbCrLf & vbCrLf
        OverlapError = True
    End If
    rst.MoveNext
Loop
rst.Close
Set rst = Nothing
If OverlapError Then
    MsgBox strErrorMsg
    BookingsOverlap = True
Else
    BookingsOverlap = False
    MsgBox "Booking Ok"
End If
End Function

This code generates a message that tells which bookings conflict. If you only need a Yes/No answer you could simplify this code a bit.

 

[arnelgp]

Checking the start date alone won't be enough.

you can, just check if its is in between the two date fields on your table/query.

 

[MackMan]

Arne & Sneu, thank you! Both worked. Sneu, I was on the right track originally, and I had something quite similar I was working on... but would have taken me a week to get there.

Arne, I didn't think of using a 'D' - function. Seems I always thought it would achieve the opposite of what I was doing.

Again thanks to both. !! Really appreciate the time you've taken.

Beers are on me!