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 - Private Sub Leave_Start_Date_BeforeUpdate(Cancel As Integer)
-
Dim strDate As String
-
-
'We format the date string to include the '#' delimiters
-
strDate = Format(CDate(Me.Leave_Start_Date), "\#m/d/yyyy\#")
-
Cancel = DMin("(" & strDate & " Between [Leave Start Date] And [Leave End Date])", _
-
"[Trial]", _
-
"([Staff Number] =" & Me.Staff_Number & ")") Or _
-
DMin("(" & strDate & " Between [Leave Start Date] And [Leave End Date])", _
-
"[Trial]", _
-
"([Staff Number]=" & Me.Staff_Number & ")")
-
If Cancel Then _
-
Call MsgBox("This date overlaps with an existing date range", _
-
vbOKOnly)
-
-
End Sub
10 8037 - Private Sub Leave_Start_Date_BeforeUpdate(Cancel As Integer)
-
Dim strDate As String
-
-
'We format the date string to include the '#' delimiters
-
strDate = Format(CDate(Me.Leave_Start_Date), "\#m/d/yyyy\#")
-
Cancel = DMax("(" & strDate & " Between [Leave Start Date] And [Leave End Date])", _
-
"[Trial]", _
-
"([Staff Number] =" & Me.Staff_Number & ")") Or _
-
DMin("(" & strDate & " Between [Leave Start Date] And [Leave End Date])", _
-
"[Trial]", _
-
"([Staff Number]=" & Me.Staff_Number & ")")
-
If Cancel Then _
-
Call MsgBox("This date overlaps with an existing date range", _
-
vbOKOnly)
-
-
End Sub
Code Adjustment
You can check both start and end date in one go: - Dim ThisStartDate As String
-
Dim ThisEndDate As String
-
Dim Criteria As String
-
Dim Cancel As Boolean
-
-
ThisStartDate = "#" & Format(Me!Leave_Start_Date, "yyyy\/mm\/dd") & "#"
-
ThisEndDate = "#" & Format(Me!Leave_End_Date, "yyyy\/mm\/dd") & "#"
-
Criteria = "[Staff Number] = " & Me!Staff_Number & " And " & _
-
"[Leave Start Date] <= " & ThisEndDate & " And [Leave End Date] >= " & ThisStartDate & ""
-
Cancel = Not IsNull(DLookup("[Staff_Number]", "[Trial]", Criteria))
Thanks a lot it's working <3
twinnyfo 3,653
Recognized Expert Moderator Specialist
cactusdata got the skills!!!
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.) -
Private Sub btnCheckDates_Click()
-
-
Dim ThisStartDate As String
-
Dim ThisEndDate As String
-
Dim Criteria As String
-
Dim Cancel As Boolean
-
-
ThisStartDate = "#" & Format(Me!Arrival, "yyyy\/mm\/dd") & "#"
-
ThisEndDate = "#" & Format(Me!Departure, "yyyy\/mm\/dd") & "#"
-
Criteria = "[Condo] = " & Me!Condo & " And " & _
-
"[Departure] <= " & ThisEndDate & " And [Departure] >= " & ThisStartDate & ""
-
Cancel = Not IsNull(DLookup("[Condo]", "[qryBookings]", Criteria))
-
-
End Sub
-
I have an issue at - 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!
Condo is alphanumeric (text), it seems, thus quotes are needed. So, try: - Criteria = "[Condo] = '" & Me!Condo & "' And " & _
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! -
Private Sub btnCheckDates_Click()
-
-
Dim ThisStartDate As String
-
Dim ThisEndDate As String
-
Dim Criteria As String
-
Dim Cancel As Boolean
-
-
ThisStartDate = "#" & Format(Me!Arrival, "yyyy\/mm\/dd") & "#"
-
ThisEndDate = "#" & Format(Me!Departure, "yyyy\/mm\/dd") & "#"
-
Criteria = "[Condo] = '" & Me!Condo & "' And " & _
-
"[Departure] <= " & ThisEndDate & " And [Departure] >= " & ThisStartDate & ""
-
Cancel = Not IsNull(DLookup("[Condo]", "[qryBookings]", Criteria))
-
If Cancel Then _
-
Call MsgBox("Double Booking", _
-
vbOKOnly)
-
-
End Sub
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).
You miss to check for both Arrival and Departure.
See the answer above.
Sign in to post your reply or Sign up for a free account.
Similar topics |
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, during the processing
time.
The first question is: Is it possible to have an overlap window? (I create
my windows by the "CreateWindowEx" command) By which command
|
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 an individual that overlap, creating all sorts of problems. One record is
entered for each scheduled event (each one is a day in length or more), so that an
individual will have multiple events on the table.
For the purposes of this question,...
|
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 stay in their original place.
Thanks,
Jim.
|
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 = document.createElement("STYLE");
oStyle.type = "text/css";
var txt = document.createTextNode("@import
url(extras/styles_large.css);")
|
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. The algo needs to catch:
(10, 20) (15, 25)
(15, 25) (10, 20)
(10, 25) (15, 20)
and
| |
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 prevent it in the output
on all of the browsers.
basically i have a page that has absolute layers (cannot be relative)
with the coordinates top/left in the db. but sometimes on certain
pages the layers overlap. I cannot set a static size for those...
|
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 the text
unreadable. Is this a known IE issue, or is there a fix for this?
Here's a simple example:
<html>
<head>
<style type="text/css">
|
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 stored in an object is read from another object that
overlaps in any way
the storage of the first object, then the overlap shall be exact and
the two objects shall
|
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 a "play button"
image with size 50 x 50 on top of the bigger image which is a video
thumbnail size 200 x 150).
1) Use <div><img ><img ></divwith the play button as the second
image and displayed relatively positioned to overlap the first...
|
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 right end of the second sequence.
my sequences are: s1= "CGATTCCAGGCTCCCCACGGGGTACCCATAACTTGACAGTAGATCTC"
s2= "GGCTCCCCACGGGGTACCCATAACTTGACAGTAGATCTCGTCCAGACCCCTAGC"
my function should be named def getOverlap(left, right)
and should return...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |