Problem with date range in table

[Guest_imported]

I am creating a database for a rental home company to keep track of their bookings. The RESERVE table should include CLIENT_ID, HOME_ID, and the dates when the home has been reserved and for how long. My problem is that I cannot seem to block of a date range. If one client books the home for one week starting Feb.12 2002, one should not be able to enter another client starting the next day.
Any suggestions?

 

[ghubbell]

Hi Willibrord,
I think you might be better to use a "start date" and an "end date".

You'll have to do a bit of coding or run a query to test, but basically the test would happen as the next "start date" is entered. In English I'd word the query like:

"Find the last end date for home id 1001 and if it is greater than the entry I just made, wipe out the entry I just made and tell me why."

Think about it and write back if you need some info on building a little procedure or query to do this. Gord
ghubbell@total.net

 

[RickSpr]

I think what you want is a way to detect whether a given range of dates (expressed as a start date and a number of days) overlaps another range. Here's a VBA function that will do that:

    Public Function DatesOverlap(Date1 As Date, Days1 As Integer, _
                                 Date2 As Date, Days2 As Integer) _
                                 As Boolean
        Dim EndDate1 As Date, EndDate2 As Date
    
        EndDate1 = DateAdd("d", Days1 - 1, Date1)
        EndDate2 = DateAdd("d", Days2 - 1, Date2)
        DatesOverlap = (EndDate2 < Date1) Or (Date2 > EndDate1)
    End Function

You need to put this function in a standard module (not a form module).

Now let's say you have your RESERVE table with fields HOME_ID, CLIENT_ID, START_DATE, and NUMBER_DAYS.

You have a reservation form "frmReservations" that you use to enter and check new reservations. It has controls "txtHome" with the desired home ID, "txtStartDate" with the date desired, and "txtNumDays" with the number of days desired.

The following code fragment shows how you could use the DatesOverlap() function to avoid scheduling overlapping reservations. I tested it in the form's BeforeUpdate event, but it could be adapted to run from a Check Dates command button, for instance.

    Private Sub Form_BeforeUpdate(Cancel As Integer)
        If DLookup(&quot;COUNT(*) <> 0&quot;, &quot;RESERVE&quot;, _
            &quot;([HOME_ID] = Forms!frmReservations!txtHome) And &quot; _
            & &quot;Not DatesOverlap([START_DATE], [NUMBER_DAYS], &quot; _
            & &quot;Forms!frmReservations![txtStartDate], &quot; _
            & &quot;Forms!frmReservations![txtNumDays])&quot;) <> 0 Then
            Beep
            MsgBox &quot;Prior reservation exists for those dates&quot;
            Cancel = True
        End If
    End Sub

Rick Sprague