Overlapping Reservations (1 Viewer)

[mafhobb]

Hi.
So I have a db where I collect reservations for a few properties and I need to find a way to test if a new reservation overlaps an existing one.

ID	PropertyName	GuestName	CheckInDate	CheckOutDate	ReservationSource	CleaningAfter	Status
1​	Son Bota	Owners	03-03-21​	07-03-21​	5	No	Active
7​	Son Bota	Eve	30-05-22​	17-06-22​	2	Yes	Cancelled
8​	Son Bota	Frank	17-06-21​	24-06-21​	1	Yes	Active
9​	Son Bota	Owners	24-06-21​	24-07-21​	5	Yes	Active
10​	Son Bota	Mark	24-07-21​	07-08-21​	2	Yes	Cancelled
12​	Portoluz, 72	Imke	19-03-21​	27-03-21​	2	Yes	Active
13​	Portoluz, 72	Cynthia	07-05-21​	15-05-21​	2	Yes	Active
14​	Alconasser Dream	Dolores	15-03-21​	22-03-21​	2	Yes	Active
15​	Alconasser Dream	Hans	01-07-21​	07-07-21​	2	Yes	Active
16​	Alconasser Dream	Johann	06-07-21​	18-07-21​	2	Yes	Active
17​	Alconasser Dream	Nika	05-08-21​	11-08-21​	2	Yes	Cancelled
30​	Torreta	Joyce	18-03-21​	27-03-21​	2	Yes	Active
32​	Torreta	Carolin	08-08-21​	19-08-21​	2	Yes	Cancelled
33​	Torreta	Pauline	26-03-21​	04-04-21​	4	Yes	Active
34​	Torreta	Phil	12-09-21​	21-09-21​	4	Yes	Cancelled

In short, I have this table above in a db and what I need to do is to run a query (I imagine) that checks all reservations at each specific property, then checks which reservations are "Active" and then compares them with each other to find if they overlap.
Quick thing though: Reservations for any unique property can start and end on the same day, such as reservations 8 and 9, with "Son Bota". It is only when the dates really go past each other, such as with reservations 15 and 16 on property "Alconasser Dream" or 30 and 33 in "Torreta" that it is considered an overlap.
What do I need to do to, 1) Get a warning when a new reservation is entered shich overlaps an existing one, and 2) A list of any overlapping reservations after pressing a button?
Thanks in advance!
mafhobb

 

[jdraw]

See this overlapping records from Paul.

 

[mafhobb]

See this overlapping records from Paul.

Hi JDraw. Thank you for the suggestion.
Yes, I knew about this query; this one lists the reservations in a specific time period, but I already had that worked out. What I need is one to find reservations that overlap other reservations for each unique property.
Any suggestions on that?
mafhobb

 

[jdraw]

Try this sample/demo

 

[MarkK]

Another thing you can do is join the same table onto itself, but specify a range of values rather than exact equalities, as the join expression. Consider something like....

SELECT list.*
FROM tReservation AS list INNER JOIN tReservation As test 
    ON list.CheckInDate <= test.CheckOutDate AND list.CheckOutDate >= test.CheckInDate AND list.Property = test.Property
WHERE list.Status = "Active"
   AND test.ID = 33

See how that takes the record you want to test and compares it back to the same table it comes from. In this way you can return rows that are somewhat like the row you are testing, but not exactly.

 

[arnelgp]

see also this demo, view qryOverlapping.

 

[Mike Krailo]

Using Arnel's function in a different way so as to avoid entering overlapping date ranges to begin with makes more sense than using a button to show all the overlaps of bad data in your reservation entries. I'm using it to validate the entered date range before allowing the record to get saved.

 

[Eugene-LS]

. I'm using it to validate the entered date range before allowing the record to get saved.

And one more:

 

[Mike Krailo]

Eugene, that's pretty slick except that isn't showing overlapping dates, it's showing touching dates as well. The OP said it was OK for two dates to be on the same such as when one party is checking out and when the other is checking in on the same date. All the data that was entered in on my example were valid dates that had no overlap. Also, if the before update is doing it's job, there will never be any overlap, unless there is importing directly to the table which would defeat the whole purpose of keeping bad data out.

 

[Eugene-LS]

The OP said it was OK for two dates to be on the same such as when one party is checking out and when the other is checking in on the same date.

Thank you for correcting me.
The solution is very simple:
In the module "Module1" change lines 24 and 25 to:

    sDateStart = Format(vDateStart + 1, "\#mm\/dd\/yyyy\#")
    sDateEnd = Format(vDateEnd - 1, "\#mm\/dd\/yyyy\#")

 

[Mike Krailo]

That will do it. Maybe the OP can chime in here and tell us if any of this helped or not.

 

[mafhobb]

Thank you for correcting me.
The solution is very simple:
In the module "Module1" change lines 24 and 25 to:

    sDateStart = Format(vDateStart + 1, "\#mm\/dd\/yyyy\#")
    sDateEnd = Format(vDateEnd - 1, "\#mm\/dd\/yyyy\#")

Thanks!
This is pretty close to what I was looking for.
Question1: Is there a need to keep mModule on V02? What does it do?
Question 2: Is there any way to only list the conflicting reservations in the query? I tested <>"" in the criteria and it worked. Any reason not to use this?
Question 3: I have also added the field "Status" in the query so I can add <>"Cancelled" so conflicting, cancelled reservations do not show in the results. Any reason not to do this?
Question 4: What is the purpose of the loop i=i+1 in Module1? Are we looking only at the first 30 conflicts?
mafhobb

 

[Eugene-LS]

Question1: Is there a need to keep mModule on V02? What does it do?

No, you can place function in any extended module .

Question 2: Is there any way to only list the conflicting reservations in the query?

Yes, by query like below

SELECT Reservations.*, GetCross([ID],[PropertyName],[CheckInDate],[CheckOutDate]) AS [Overlapping Reservations]
FROM Reservations
WHERE (((GetCross([ID],[PropertyName],[CheckInDate],[CheckOutDate]))<>""));

 

[Mike Krailo]

Question1: Is there a need to keep mModule on V02? What does it do?

Edit: I assume you mean mFunctions module.

That has Arnel's original functions in there and I used it to validate the reservations dates BEFORE you enter them into the database. I was waiting for you to reply back but there was a bit of a delay there. If you never put bad data into your table with overlapping date ranges, then there is no need to worry about it or do any post queries on it with a button.

So if you want that, then YES, keep the mFunctions module because it is used in the BeforeUpdate code of the form itself. Go ahead and try to enter in an overlapping date range in using the Reservations form. Do not use the table to input data, there is no validation there.

Edit: you can however dump the Query1 since that is totally not needed.

 

[mafhobb]

That has Arnel's original functions in there and I used it to validate the reservations dates BEFORE you enter them into the database. I was waiting for you to reply back but there was a bit of a delay there. If you never put bad data into your table with overlapping date ranges, then there is no need to worry about it or do any post queries on it with a button.

So if you want that, then YES, keep the mFunctions module because it is used in the BeforeUpdate code of the form itself. Go ahead and try to enter in an overlapping date range in using the Reservations form. Do not use the table to input data, there is no validation there.

OK, So if I want to validate the dates as I enter them, then I simply call for public function isoverlapped on before update for the new reservation? But how does this function account for the different properties?
Then, if at some point I need to test al the reservations, I call for GetCross?
mafhobb

 

[Mike Krailo]

Private Sub Form_BeforeUpdate(Cancel As Integer)
   If IsAvailable(Me!PropertyName, Me!CheckInDate, Me!CheckOutDate) <> True Then
      MsgBox "The date range entered overlapps with an Active reservation!"
      Cancel = True
   End If
End Sub

'vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
' Purpose   :  Test if entered date range for a given rental property is available (no overlap)
' Usage     :  IsAvailable(Me!PropertyName, Me!CheckInDate, Me!CheckOutDate)
' Requires  :  IsOverlapped
' Inputs    :  PropertyName, StartDate, EndDate
' Ouputs    :  True/False
' CreatedBy :  Mike Krailo on 03/14/2022 11:36 PM
'vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
'
Public Function IsAvailable(Rental As String, CheckIn As Date, CheckOut As Date) As Boolean
   Dim StrSQL As String
   Dim rst As DAO.Recordset
   Dim db As DAO.Database
   Set db = CurrentDb

   StrSQL = "SELECT PropertyName, CheckInDate, CheckOutDate " _
          & "FROM Reservations WHERE Status='Active'"
       
   Set rst = db.OpenRecordset(StrSQL)
   With rst
      If .RecordCount > 0 Then
         Do Until .EOF
            If IsOverlapped(CheckIn, CheckOut, !CheckInDate, !CheckOutDate) = True Then
               IsAvailable = False
               Exit Do
            Else
               IsAvailable = True
            End If
            .MoveNext
         Loop
      Else
         IsAvailable = True
      End If
   End With
   rst.Close
   Set rst = Nothing
   Set db = Nothing
End Function

The IsOverlapped function is used in the form function IsAvailable as shown above. When you fill out the data, the first thing has to be filled out is the property. I am assuming one property per reservation. If you can rent more than one property per reservation, then that complicates everything.

You should probably do even more validation to make sure everything is entered properly and you don't get any bad data. Hope that makes sense.

 

[MarkK]

You can perform that IsOverlapped logic much more efficiently in a query, rather than looping thru and calling an external function for every row in the recordset. Consider code like...

Public Function IsConflict(PropertyName As String, CheckIn As Date, CheckOut As Date) As Boolean
    Const SQL As String = _
        "SELECT Count(*) " & _
        "FROM Reservations AS t " & _
        "WHERE t.PropertyName = [prmPropertyName] " & _
            "AND t.Status = 'Active' " & _
            "AND t.CheckOutDate >= [prmInDate] " & _
            "AND t.CheckInDate <= [prmOutDate];"
            
    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters("prmPropertyName") = PropertyName
        .Parameters("prmInDate") = CheckIn
        .Parameters("prmOutDate") = CheckOut
        With .OpenRecordset
            If Not .EOF Then IsConflict = .Fields(0)
        End With
    End With
    
End Function

In general, open exactly the recordset you need, rather than a really broad one you need to iterate thru.
hth

 

[Mike Krailo]

The parameter query you created will work nicely. And thanks for showing a way to eliminate the external function. That's slick.

 

[Mike Krailo]

Well MarkK, that code didn't actually work after some testing. The only way I could get it to work was by adding another parameter for the record ID to check and exclude the current record from checking itself.

Public Function IsConflict(PropertyName As String, CheckIn As Date, CheckOut As Date, ID As Long) As Boolean
   Const SQL As String = _
      "SELECT Count(*) " & _
      "FROM Reservations AS t " & _
      "WHERE t.PropertyName = [prmPropertyName] " & _
      "AND t.Status = 'Active' " & _
      "AND t.CheckOutDate >= [prmInDate] " & _
      "AND t.CheckInDate <= [prmOutDate] " & _
      "AND t.ID <> [prmID];"
            
   With CurrentDb.CreateQueryDef("", SQL)
      .Parameters("prmPropertyName") = PropertyName
      .Parameters("prmInDate") = CheckIn
      .Parameters("prmOutDate") = CheckOut
      .Parameters("prmID") = ID
      With .OpenRecordset
         If .Fields(0) > 0 Then
            IsConflict = True
         Else
            IsConflict = False
         End If
      End With
   End With
End Function

Then in the calling function use = instead of <> and of course add the ID parameter.

Private Sub Form_BeforeUpdate(Cancel As Integer)
   ' Do not allow new overlapping reservations for given property name
   If Nz(Me!PropertyName, vbNullString) <> vbNullString Then
      If IsConflict(Me!PropertyName, Me!CheckInDate, Me!CheckOutDate, Me!ID) = True Then
         MsgBox "The date range entered overlapps with an Active reservation!"
         Cancel = True
      End If
   Else
      'Ensure the PropertyName field is filled out
      MsgBox "The Property Name field cannot be empty!"
      Cancel = True
   End If
End Sub

 

[MarkK]

Well MarkK, that code didn't actually work after some testing. The only way I could get it to work was by adding another parameter for the record ID to check and exclude the current record from checking itself.

My Function IsConflict() was intended to be used before adding the potentially conflicting row. If you want to run a test for existing rows, look at the SQL in my post #5. In that case, since the row to test already exists, all you need to supply is the ID, which simplifies your SQL and function signature even further...

Public Function IsConflictExistingRow(ID As Long) As Boolean
    Const SQL As String = _
        "SELECT Count(*) " & _
        "FROM tReservation AS list INNER JOIN tReservation As test " & _
            "ON list.CheckInDate <= test.CheckOutDate " & _
            "AND list.CheckOutDate >= test.CheckInDate " & _
            "AND list.Property = test.Property " & _
        "WHERE List.Status = 'Active' " & _
        "AND test.ID = p0 "
            
    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters(0) = ID
        With .OpenRecordset
            If Not .EOF Then IsConflictExistingRow = .Fields(0)
      End With
   End With
  
End Function

That is pretty tricky SQL though, because it does the comparisons in the join expression!!! That SQL will not be supported in the Query Designer in Design View, but it is worthy of mention in this case, I think, because it very much simplifies finding rows that have overlapping values, not exact equalities, as is usually the case when you join tables.

Also, with respect to booleans, this code...

With .OpenRecordset
    If .Fields(0) > 0 Then
        IsConflict = True
    Else
        IsConflict = False
    End If
End With

...is functionally equivalent to this code...

With .OpenRecordset
     IsConflict = .Fields(0)
End With

Returning a boolean from an If...Else...End If block is like multiplying an integer by one; it makes no difference. If .Fields(0) > 0 is true, the If block returns true, if .Fields(0) > 0 is false, the If block returns false. Just return the expression. It is already the boolean you need.

Similarly with comparison to zero. .Fields(0) is a number that will be coerced to a boolean. If that number is zero, the boolean will be false. If that number is non-zero, the boolean returned will be true. As a result, the conversion of an integer to a boolean already does an implicit comparison with zero, so you don't have to.

: )