472,950 Members | 2,358 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,950 software developers and data experts.

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

269 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 16548
32,546 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
269 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
269 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:
This is what I tried for my DB. (This is NOT working).
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnCheckDates_Click()
  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)
  15.               Dim rsDao As DAO.Recordset
  16.               Set rsDao = CurrentDb.OpenRecordset("SELECT * FROM [tblBookings] WHERE " & strWhere, dbOpenDynaset)
  19.               'Move to last record to ensure that recordset has been populuated.
  20.               'This is needed because we wish to access the recordcount property
  23.               If rsDao.RecordCount = 0 Then
  24.                   'Booking is ok.
  25.                   DoCmd.Save
  26.                   Exit Sub
  27.               Else
  28.                  'Booking is not ok
  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"))
  35.                       rsDao.MoveNext
  36.                   Loop
  38.               End If
  40.               'Cancel entry
  41.                   Me.Undo
  43.               'Inform user
  44.                   strMsg = "DanicaDear, you made a double booking!" & strMsg
  45.                   MsgBox strMsg
  47.               'Cleanup
  48.                   Set rsDao = Nothing
  51.               End With
  52.       End If
  55. End Sub
"Run-time error 3061. Too few parameters. Expected 1."

However, I did get THIS piece of code to work:
Referenced thread:

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnCheckDates_Click()
  3. Dim ThisStartDate   As String
  4. Dim ThisEndDate     As String
  5. Dim Criteria        As String
  6. Dim Cancel          As Boolean
  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)
  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
32,546 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
32,546 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

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

Similar topics

by: rong.guo | last post by:
Hi Group! I am struggling with a problem of giving a date range given the start date. Here is my example, I would need to get all the accounts opened between each month end and the first 5...
by: PromisedOyster | last post by:
Is there a way that I can get a resultset that contains unique dates in a given date range without the need to have a temporary table and a cursor? perhaps something like: declare @start_date...
by: Cy | last post by:
I have a custom access 2000 database, that has contracts with starting and ending dates. What I'd like to be able to do, is key in a date, say today 9/2/05 and get a list of all "current"...
by: rugger81 | last post by:
I am currently working in the sql server 2000 environment and I want to write a function to pull all dates within a given date range. I have created several diferent ways to do this but I am...
by: rcamarda | last post by:
I wish to build a table based on values from another table. I need to populate a table between two dates from another table. Using the START_DT and END_DT, create records between those dates. I...
by: valve2nd | last post by:
This is probably a bonehead question, but I can't make it work. I want to prompt the user for a single date and select all records with a date falling within the week prior. Obviously this works:...
by: tonialbrown | last post by:
I have an Sql update statement that I am using that updates the data from a record chosen by the user from a list box lstDelFrom. This is working for all the text fields & updates fine. Once I add...
by: EORTIZ | last post by:
I have 2 tables: Claims table- claim ID, Member_ID, service_from_date, service_end_date. Plan table- Member_ID, Plan_ID, Plan_from_date, Plan_end_date. The same member_id can have multiple...
by: dp Colgan | last post by:
Hi, I see alot of examples of returning the count of but was wondering how I return the actual dates of the weekends in a particular range. Can anyone provide an example.
by: viveknarayan1 | last post by:
I need to modify a form which uses a single date entry to a date range and populate the table with all dates between those dates.
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.