470,593 Members | 2,510 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.

Days overlap

11 Byte
Hey Guys

I'm trying to set up a form to register employee vacations without overlapping days it worked fine however in the below case it doesn't detect an overlap

If I entered a vacation from Oct 2nd 2020 until Oct 4th 2020
Then I entered vacation from Oct 1st 2020 until Oct 5th 2020 for the same employee ID

I've done the code for both text box Start & end

I want it to detect this sort of overlap when I adjust the Leave End Date box

Expand|Select|Wrap|Line Numbers
  1. Private Sub Leave_Start_Date_BeforeUpdate(Cancel As Integer)
  2.     Dim strDate As String
  3.  
  4.     'We format the date string to include the '#' delimiters
  5.     strDate = Format(CDate(Me.Leave_Start_Date), "\#m/d/yyyy\#")
  6.     Cancel = DMin("(" & strDate & " Between [Leave Start Date] And [Leave End Date])", _
  7.                   "[Trial]", _
  8.                   "([Staff Number] =" & Me.Staff_Number & ")") Or _
  9.              DMin("(" & strDate & " Between [Leave Start Date] And [Leave End Date])", _
  10.                   "[Trial]", _
  11.                   "([Staff Number]=" & Me.Staff_Number & ")")
  12.     If Cancel Then _
  13.         Call MsgBox("This date overlaps with an existing date range", _
  14.                     vbOKOnly)
  15.  
  16. End Sub
Nov 8 '20 #1

✓ answered by cactusdata

You can check both start and end date in one go:

Expand|Select|Wrap|Line Numbers
  1. Dim ThisStartDate   As String
  2. Dim ThisEndDate     As String
  3. Dim Criteria        As String
  4. Dim Cancel          As Boolean
  5.  
  6. ThisStartDate = "#" & Format(Me!Leave_Start_Date, "yyyy\/mm\/dd") & "#"
  7. ThisEndDate = "#" & Format(Me!Leave_End_Date, "yyyy\/mm\/dd") & "#"
  8. Criteria = "[Staff Number] = " & Me!Staff_Number & " And " & _
  9.     "[Leave Start Date] <= " & ThisEndDate & " And [Leave End Date] >= " & ThisStartDate & ""
  10. Cancel = Not IsNull(DLookup("[Staff_Number]", "[Trial]", Criteria))

10 7525
Lou699
11 Byte
Expand|Select|Wrap|Line Numbers
  1. Private Sub Leave_Start_Date_BeforeUpdate(Cancel As Integer)
  2. Dim strDate As String
  3.  
  4. 'We format the date string to include the '#' delimiters
  5. strDate = Format(CDate(Me.Leave_Start_Date), "\#m/d/yyyy\#")
  6. Cancel = DMax("(" & strDate & " Between [Leave Start Date] And [Leave End Date])", _
  7. "[Trial]", _
  8. "([Staff Number] =" & Me.Staff_Number & ")") Or _
  9. DMin("(" & strDate & " Between [Leave Start Date] And [Leave End Date])", _
  10. "[Trial]", _
  11. "([Staff Number]=" & Me.Staff_Number & ")")
  12. If Cancel Then _
  13. Call MsgBox("This date overlaps with an existing date range", _
  14. vbOKOnly)
  15.  
  16.  End Sub
Code Adjustment
Nov 8 '20 #2
cactusdata
199 Expert 128KB
You can check both start and end date in one go:

Expand|Select|Wrap|Line Numbers
  1. Dim ThisStartDate   As String
  2. Dim ThisEndDate     As String
  3. Dim Criteria        As String
  4. Dim Cancel          As Boolean
  5.  
  6. ThisStartDate = "#" & Format(Me!Leave_Start_Date, "yyyy\/mm\/dd") & "#"
  7. ThisEndDate = "#" & Format(Me!Leave_End_Date, "yyyy\/mm\/dd") & "#"
  8. Criteria = "[Staff Number] = " & Me!Staff_Number & " And " & _
  9.     "[Leave Start Date] <= " & ThisEndDate & " And [Leave End Date] >= " & ThisStartDate & ""
  10. Cancel = Not IsNull(DLookup("[Staff_Number]", "[Trial]", Criteria))
Nov 9 '20 #3
Lou699
11 Byte
Thanks a lot it's working <3
Nov 9 '20 #4
twinnyfo
3,653 Expert Mod 2GB
cactusdata got the skills!!!
Nov 9 '20 #5
DanicaDear
267 256MB
Is it appropriate for me to ask a question with my own code which I used this thread to build?
I figure the experts are already on this thread. I am trying to accomplish the exact same thing where "Condo" replaces "Staff Member" and Arrival and Departure are names of my start and end dates.

Arrival and Departure are Date/Time Fields. Condo is a short text field.
I made a button to click to run the check after I enter Arrival, Departure, and Condo onto my form. (The screen shot attached may be helpful.)

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, "yyyy\/mm\/dd") & "#"
  9. ThisEndDate = "#" & Format(Me!Departure, "yyyy\/mm\/dd") & "#"
  10. Criteria = "[Condo] = " & Me!Condo & " And " & _
  11.     "[Departure] <= " & ThisEndDate & " And [Departure] >= " & ThisStartDate & ""
  12. Cancel = Not IsNull(DLookup("[Condo]", "[qryBookings]", Criteria))
  13.  
  14. End Sub
  15.  
I have an issue at
Expand|Select|Wrap|Line Numbers
  1. Criteria = "[Condo] = " & Me!Condo & " And " & _
specifically the [Condo] in brackets. I get a run-time error when I run it:
The expression you entered as a query parameter produced this error: 'A104'
(A104 is the condo I chose for the test.). If I change [Condo] to [test] I get the same run time error with "test" in the error instead of A104. I am attaching a screen shot for clarity.

The "Trial" in the original post threw me way off because I wasn't sure what that was supposed to be. I inserted my query name there that contains all my bookings for ALL condos. I hope that was correct.

Can someone help me resolve this error so I can try to fire this code and detect my double bookings!?? :-)

Also, if I may ask: The post said that this will check for BOTH arrival and departure with this one piece of code. Is that correct if I am running it from a button, and not on BOTH the arrival AND the departure fields when updated? I feel that I may have taken something and oversimplified it. I need to check that the arrival date is not in the range of another reservation, and then I also need to check that the departure date is not in the range of another reservation. And I also need to make sure no dates overlap---like the original poster's example... if I have a reservation existing already for Oct. 10-15, and then I make a reservation for Oct. 8-17, the arrival date and departure date would pass the check but the middle dates are still overlapped.

Thanks in advance for anyone's time and help!
Attached Images
File Type: png 2022-03-20 09_47_46-Beach Sanity Software.png (75.0 KB, 8 views)
Mar 20 '22 #6
cactusdata
199 Expert 128KB
Condo is alphanumeric (text), it seems, thus quotes are needed. So, try:

Expand|Select|Wrap|Line Numbers
  1. Criteria = "[Condo] = '" & Me!Condo & "' And " & _
Mar 20 '22 #7
DanicaDear
267 256MB
cactusdata. Thank you. I did get the code to fire and I added a message box
Just for anyone else looking in the future, here was my final piece. This is BRILLIANT and will help me so much. Thank you soo much; I am so grateful!
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, "yyyy\/mm\/dd") & "#"
  9. ThisEndDate = "#" & Format(Me!Departure, "yyyy\/mm\/dd") & "#"
  10. Criteria = "[Condo] = '" & Me!Condo & "' And " & _
  11.     "[Departure] <= " & ThisEndDate & " And [Departure] >= " & ThisStartDate & ""
  12. Cancel = Not IsNull(DLookup("[Condo]", "[qryBookings]", Criteria))
  13. If Cancel Then _
  14. Call MsgBox("Double Booking", _
  15. vbOKOnly)
  16.  
  17. End Sub
Mar 20 '22 #8
DanicaDear
267 256MB
I'm very sorry that I'm back.
As I began testing this with data, nothing is firing correctly.

I made a reservation for 1/1/25-1/5/25.
I tested these dates. I never got any message box.
Tests:
12/31/24 – 1/1/25 fires correctly. This is NOT a double booking, therefore I don't expect a message box.
12/31/24 - 1/2/25 does not detect double booking
12/31/24 – 1/3/25 does not detect double booking
12/31/24 – 1/4/25 does not detect double booking
12/31/24 – 1/5/25 does not detect double booking
12/31/24-1/6/25 does not detect double booking
1/1/25 – 1/2/25 does not detect double booking
1/1/25 – 1/3/25 does not detect double booking
1/1/25-1/4/25 does not detect double booking
1/1/25 – 1/5/25 does not detect double booking
1/1/25 – 1/6/25 does not detect double booking
1/2/25 - 1/3/25 does not detect double booking
1/2/25-1/4/25 does not detect double booking
1/2/25 – 1/5/25 does not detect double booking
1/2/25-1/6/25 does not detect double booking
1/3/25-1/4/25 does not detect double booking
1/3/25-1/5/25 does not detect double booking
1/3/25 – 1/6/25 does not detect double booking

Do you see anything else that was wrong? I had some double booking message boxes a while ago but when I saved and came back later, now I can't get ANY! My code is posted in the previous thread, but I'm still not confident that code will completely work for what I'm testing because even on my first test it did not detect everything correctly.
If me.Arrival OR Me.Departure falls within any previously saved range of arrival - departure for the Me.Condo in qryBookings, I need the message box to fire. It WOULD BE OK if Me.Arrival = another Departure and Me.Departure = another arrival (because people do come and go on the same day).
Mar 20 '22 #9
cactusdata
199 Expert 128KB
You miss to check for both Arrival and Departure.
See the answer above.
Mar 21 '22 #10
DanicaDear
267 256MB
I do apologize!
I found a couple errors. One being my "on Click" event procedure went missing randomly...so the clicking wasn't running anything. Hence nothing being deteched.

Second error, I had departure in my code twice.
Expand|Select|Wrap|Line Numbers
  1.  "[Departure] <= " & ThisEndDate & " And [Departure] >= " & ThisStartDate & ""
Should be
Expand|Select|Wrap|Line Numbers
  1.  "[Arrival] <= " & ThisEndDate & " And [Departure] >= " & ThisStartDate & ""
and from what I can tell this one piece of code on a button will check both the start and the end dates in one test! I am using it!!! THANK YOU SO MUCH!!!
Mar 21 '22 #11

Post your reply

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

Similar topics

reply views Thread by dag | last post: by
2 posts views Thread by John Baker | last post: by
reply views Thread by JIM.H. | last post: by
1 post views Thread by Andrew Poulos | last post: by
6 posts views Thread by Robin Haswell | last post: by
6 posts views Thread by ralphJake | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.