473,320 Members | 2,027 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

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 7972
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
214 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
269 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, 43 views)
Mar 20 '22 #6
cactusdata
214 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
269 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
269 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
214 Expert 128KB
You miss to check for both Arrival and Departure.
See the answer above.
Mar 21 '22 #10
DanicaDear
269 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

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

Similar topics

0
by: dag | last post by:
Hi! I would like to do an overlap window, over my main window (of my application), with a Progress Bar. Exactly when I push a button of my application I want show a window, with a Progress bar,...
2
by: John Baker | last post by:
HI; I have a table where the user is entering data which shows scheduling for member of teams. One problem we have encountered is that sometimes the inputter (is this a word) puts in dates for...
0
by: JIM.H. | last post by:
Hello, I have a ReportViewer and a DropDownList controls in my application. In the design view I see they do not overlap but when I run the application they overlap each other, how can I get them...
1
by: Andrew Poulos | last post by:
I'm using the following to dynamically add a style sheet with larger font sizes: if (document.createStyleSheet) { document.createStyleSheet("extras/styles_large.css"); } else { var oStyle =...
6
by: Robin Haswell | last post by:
Hey guys I was wondering if you could give me a hand with something. If I have two tuples that define a range, eg: (10, 20), (15, 30), I need to determine whether the ranges overlap each other....
6
by: ralphJake | last post by:
hi :} i have been looking for a script like this for a few weeks now, and i am finally giving up and asking for help. everything i found involved preventing overlap in dreamweaver, i want to...
0
by: ltlpeepl | last post by:
I'm having the issue where I have floating spans that overlap in IE6 when wrapping should occur. If you make the browser window small enough so that wrapping will occur, they will overlap, making...
13
by: Mike S | last post by:
I came across the following paragraph in the "Semantics" section for simple assignment in N1124 (C99 draft) and I'm wondering if I'm interpreting it right: 6.5.16.1p3: If the value being...
5
by: liketofindoutwhy | last post by:
It seems like there are only 4 methods to overlap 2 images using CSS? There are two images, each with its own URL. Using CSS, there seems to be 2 ways to overlap them (the task is actually to put...
6
by: AnneTanne | last post by:
I need to make a function that determines the overlap between 2 sequences, and then return the overlap. The overlap is a coherent sequence that is in the left end of the first sequence, and in the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.