470,593 Members | 2,471 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,593 developers. It's quick & easy.

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

267 256MB
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?!
Mar 21 '22 #1
5 15967
NeoPa
32,298 Expert Mod 16PB
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.
Mar 21 '22 #2
DanicaDear
267 256MB
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? :-)
Mar 21 '22 #3
DanicaDear
267 256MB
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).
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnCheckDates_Click()
  2.  
  3.  
  4.       If Me.Dirty Then
  5.           Dim strWhere As String, strMsg As String
  6.               With Me
  7.               strWhere = "(([Arrival]<#%S#) AND " & _
  8.                          "([Departure]>#%E#) AND " & _
  9.                          "([Condo]=%R))"
  10.               strWhere = Replace(strWhere, "%S", Format(.Departure, " mm/dd/yy "))
  11.               strWhere = Replace(strWhere, "%E", Format(.Arrival, " mm/dd/yy "))
  12.               strWhere = Replace(strWhere, "%R", .Condo)
  13.  
  14.  
  15.               Dim rsDao As DAO.Recordset
  16.               Set rsDao = CurrentDb.OpenRecordset("SELECT * FROM [tblBookings] WHERE " & strWhere, dbOpenDynaset)
  17.  
  18.  
  19.               'Move to last record to ensure that recordset has been populuated.
  20.               'This is needed because we wish to access the recordcount property
  21.  
  22.  
  23.               If rsDao.RecordCount = 0 Then
  24.                   'Booking is ok.
  25.                   DoCmd.Save
  26.                   Exit Sub
  27.               Else
  28.                  'Booking is not ok
  29.  
  30.                   Do While Not rsDao.EOF 'Do until we reach the eof, (End Of File)
  31.                       strMsg = strMsg & vbNewLine & "That is between [%S] and [%E]"
  32.                           strMsg = Replace(strMsg, "%S", Format(rsDao![Arrival], "mm/dd/yy"))
  33.                           strMsg = Replace(strMsg, "%E", Format(rsDao![Departure], "mm/dd/yy"))
  34.  
  35.                       rsDao.MoveNext
  36.                   Loop
  37.  
  38.               End If
  39.  
  40.               'Cancel entry
  41.                   Me.Undo
  42.  
  43.               'Inform user
  44.                   strMsg = "DanicaDear, you made a double booking!" & strMsg
  45.                   MsgBox strMsg
  46.  
  47.               'Cleanup
  48.                   Set rsDao = Nothing
  49.  
  50.  
  51.               End With
  52.       End If
  53.  
  54.  
  55. 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

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnCheckDates_Click()
  2.  
  3. Dim ThisStartDate   As String
  4. Dim ThisEndDate     As String
  5. Dim Criteria        As String
  6. Dim Cancel          As Boolean
  7.  
  8. ThisStartDate = "#" & Format(Me!Arrival, "mm/dd/yyyy") & "#"
  9. ThisEndDate = "#" & Format(Me!Departure, "mm/dd/yyyy") & "#"
  10. Criteria = "[Condo] = '" & Me!Condo & "' And " & _
  11.     "[Arrival] < " & ThisEndDate & " And [Departure] > " & ThisStartDate & ""
  12. Cancel = Not IsNull(DLookup("[Condo]", "[tblBookings]", Criteria))
  13. If Cancel Then _
  14. Call MsgBox("Danica (my) Dear:  you have double booked!", _
  15. vbOKOnly)
  16.  
  17. 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!
Mar 21 '22 #4
NeoPa
32,298 Expert Mod 16PB
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 :-)
Mar 21 '22 #5
NeoPa
32,298 Expert Mod 16PB
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.
Mar 21 '22 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

24 posts views Thread by PromisedOyster | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.