Check that date range has no dates in another existing date range: overlapping dates

[DanicaDear]

Hi everyone. It's been a while since I had to consult with Bytes and I have missed my buddies! (You know who you are!) :-)

I have an access database that I use to run my rental management company and I have several condos. I had my first double booking last weekend due to a typo so it's time I set my software to help me.

I have reviewed numerous posts and threads here...one was very close but I still felt like I need more help.

The data dumps into tblBookings. My input form is frmBookings.
My fields are
Arrival (Date/Time)
Departure (Date/Time)
Condo (short text)

When I enter data into all three fields, I want to click a button (btnCheckDates) and have it check the table for any overlapping dates for that particular Condo.
Most of the threads are just checking the first and last dates...but it's more complex.
Here is what I mean:
Say there is an existing reservation Jan. 1-Jan 5.
Any of these scenarios would be a double booking:
Dec. 31 - Jan. 2
Jan. 2-Jan. 4
Jan 3-Jan 8
Dec. 31-Jan 7

So as you can see the new record arrival date could be greater than or less than the existing arrival date.
The new departure date could be greater or less than the existing departure date.
The entire series could be outside the gap.
The entire series could be inside the gap.
And Arrival date CAN be on a Departure date. A Departure date CAN be on an arrival date.

I want to click the button, and if a date overlaps, I just want a message box that says "Double Booking."

My code writing skills are basically non-existent. But I do know my way around access fairly well by now (thanks all to Bytes!)

I am scared to death of this thread!!! But I'm ready! Who can help me?!

[NeoPa]

Hi Danica (my) Dear.

A bit late for me ATM so I'll just add a quick tip (Time Interval Overlapping (MS Access 2003)) and revisit tomorrow to see any reply.

I'll be happy to go into more detail then if you still need it :-)

PS. This is a concept that many experienced developers still struggle with but if you follow the advice/explanation I give in the linked post it should work for you perfectly -Ade.

[DanicaDear]

Actually I had found this and was studying it. I'm sure a ' or " or ' " will get me somewhere....LOL!!!
But I'll give it a go!

When are you making a trip to Florida to stay in one of my not-double-booked-condos? :-)

[DanicaDear]

Once I figured out that NeoPa was right (aaaaasssssss usual! LOL!) this became so much simpler.
I had to understand the union or X<B and Y<A.
I had made a list of 4 lines of ANDs and ORs with mathematical symbols that would scare Einstein. Each time I checked my line against NeoPa's two simple expressions, it passed check.

I did try to use the code in this thread, although unsuccessfully. My "Condo" field is short text and the referenced thread poster's field was a number. I know tick marks and quotes work differently in the VBA.

I'm going to post two things: The code I tried from two referenced posts: one is working for me, and one is not.

So for the next reader:
https://bytes.com/topic/access/answe...-a#post3655773
This is what I tried for my DB. (This is NOT working).

Private Sub btnCheckDates_Click()
 
 
      If Me.Dirty Then
          Dim strWhere As String, strMsg As String
              With Me
              strWhere = "(([Arrival]<#%S#) AND " & _
                         "([Departure]>#%E#) AND " & _
                         "([Condo]=%R))"
              strWhere = Replace(strWhere, "%S", Format(.Departure, " mm/dd/yy "))
              strWhere = Replace(strWhere, "%E", Format(.Arrival, " mm/dd/yy "))
              strWhere = Replace(strWhere, "%R", .Condo)
 
 
              Dim rsDao As DAO.Recordset
              Set rsDao = CurrentDb.OpenRecordset("SELECT * FROM [tblBookings] WHERE " & strWhere, dbOpenDynaset)
 
 
              'Move to last record to ensure that recordset has been populuated.
              'This is needed because we wish to access the recordcount property
 
 
              If rsDao.RecordCount = 0 Then
                  'Booking is ok.
                  DoCmd.Save
                  Exit Sub
              Else
                 'Booking is not ok
 
                  Do While Not rsDao.EOF 'Do until we reach the eof, (End Of File)
                      strMsg = strMsg & vbNewLine & "That is between [%S] and [%E]"
                          strMsg = Replace(strMsg, "%S", Format(rsDao![Arrival], "mm/dd/yy"))
                          strMsg = Replace(strMsg, "%E", Format(rsDao![Departure], "mm/dd/yy"))
 
                      rsDao.MoveNext
                  Loop
 
              End If
 
              'Cancel entry
                  Me.Undo
 
              'Inform user
                  strMsg = "DanicaDear, you made a double booking!" & strMsg
                  MsgBox strMsg
 
              'Cleanup
                  Set rsDao = Nothing
 
 
              End With
      End If
 
 
End Sub

"Run-time error 3061. Too few parameters. Expected 1."


However, I did get THIS piece of code to work:
Referenced thread:
https://bytes.com/topic/access/answe...ap#post3834321

Private Sub btnCheckDates_Click()
 
Dim ThisStartDate   As String
Dim ThisEndDate     As String
Dim Criteria        As String
Dim Cancel          As Boolean
 
ThisStartDate = "#" & Format(Me!Arrival, "mm/dd/yyyy") & "#"
ThisEndDate = "#" & Format(Me!Departure, "mm/dd/yyyy") & "#"
Criteria = "[Condo] = '" & Me!Condo & "' And " & _
    "[Arrival] < " & ThisEndDate & " And [Departure] > " & ThisStartDate & ""
Cancel = Not IsNull(DLookup("[Condo]", "[tblBookings]", Criteria))
If Cancel Then _
Call MsgBox("Danica (my) Dear:  you have double booked!", _
vbOKOnly)
 
End Sub

This piece of code at first did NOT work, because I had an error in it that I saw after understanding the first referenced piece. So both actually helped me solve the problem.

Now I have been around the block long enough to know that NeoPa thinks much farther around the corner than I know to do,
So if you can use his code above, that's what I would recommend. :-P

If you need to try something else..then I have shown you what worked for me.

I know NeoPa probably won't let me by with this anyway so let's see what's next. LOL!

[NeoPa]

I've replied privately with an offer to talk you through the concepts and how to implement them in your database.

I look forward to hearing from you :-)

[NeoPa]

Hi Danica.

As far as your solution goes, it accurately reflects the idea I was explaining in my linked post. It doesn't look like you have any more to learn on that front :-)

NB. We cross-posted earlier as I'd had your page open ready for a while before I posted - only to find you'd posted again in the meantime.