This works beautifully NeoPa. Thank you for the explanations, which I have studied. I added an extra message box on the arrival>departu re and it works beautifully also.
Sometimes the answers are so simple. Sometimes they are more complex. Either way, I super grateful for the help. Thank you so much!
DanicaDear
Last Activity: Nov 23 '22, 01:26 AM
Joined: Sep 29 '09
Location: Alabama
-
Double Booking Code needs a little extra jazz
Hi everybody!
I snagged a piece of code from Bytes (written by the one and only NeoPa to whom my Access success is somewhat owed!). The code works great but I desire it to do a few more things that I am not able to put together. I did TRY and you experts may laugh at my message box sequence. I laugh at myself too, and then return to Bytes. LOL!
Below, then the code runs and it IS a double booking, i get a message box that... -
DanicaDear replied to Check that date range has no dates in another existing date range: overlapping datesin AccessOnce 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"...Leave a comment:
-
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.
Should beCode:"[Departure] <= " & ThisEndDate & " And [Departure] >= " & ThisStartDate & ""
...Code:"[Arrival] <=
Leave a comment:
-
DanicaDear replied to Check that date range has no dates in another existing date range: overlapping datesin AccessActually I had found this and was studying it. I'm sure a ' or " or ' " will get me somewhere....LO L!!!
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? :-)Leave a comment:
-
DanicaDear started a topic Check that date range has no dates in another existing date range: overlapping datesin AccessCheck that date range has no dates in another existing date range: overlapping dates
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... -
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...Leave a comment:
-
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!
...Code:Private Sub btnCheckDates_Click() Dim ThisStartDate As String Dim ThisEndDate As String Dim Criteria As String Dim Cancel As Boolean
Leave a comment:
-
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...Leave a comment:
-
DanicaDear replied to How to limit DateDiff to a calendar year when data ranges extend beyond that year?in AccessNeoPa! I'm so glad to hear from you again!! I hope you are doing well. I had given up on this but will study it again perhaps. Thank you for the additional reference.
Sometimes I get in over my head. It doesn't take much, really. haha.
I'll post back if/when I get this worked out. I think the solutions here have been excellent...I just gave up to soon.Leave a comment:
-
DanicaDear replied to How to limit DateDiff to a calendar year when data ranges extend beyond that year?in AccessSo I saved all the queries as you mentioned but how do I get MY existing query talking to these? I'm pretty confused because I use the field "Arrival" and then I use "FromDate" as a parameter to limit the time the query looks at. I'm not sure if DateStart and DateFrom are supposed to be replaced with Arrival/Departure or if those are new fields just used to make your queries run.
I'm stuck! (Ugh. So sorry...Leave a comment:
-
DanicaDear replied to How to limit DateDiff to a calendar year when data ranges extend beyond that year?in AccessHoly cow! That was WAY MORE difficult than I anticipated. Thank you so much for your detailed response. I will study it.
What are the "4" days in your result on line 2? I see what the 7 days are. But I'm not following the 4.Leave a comment:
-
DanicaDear started a topic How to limit DateDiff to a calendar year when data ranges extend beyond that year?in AccessHow to limit DateDiff to a calendar year when data ranges extend beyond that year?
I want to calculate an occupancy rate for nights stayed in a hotel. The occupancy rate needs to be for a calendar year.
The fields I am working with are
Arrival
Departure
I have parameters set up in the query as criteria on the Departure field.
FromDate
ToDate
Most of the arrival dates AND departure dates fall within the year...but the reservation occupying the hotel room on January... -
twinnyfo and NeoPa, thank you so much for your time! I tried to figure this out several years ago and no luck then either. :-)Leave a comment:
-
twinnyfo is winning!
Yes, that's what I'm trying to do. :-)
It would be crazy awesome in a number of programs I've written.
NeoPa, thank you, it's always fun getting the answers I need. LOL!!Leave a comment:
-
But it's not my control...it's the Access built-in search box in the navigation bar (I think it's called navigation bar...by the forward and backward arrows at the bottom of the application.)Leave a comment:
-
In Access, can cursor default to the search box on a form?
Is it possible to have the cursor default to the Search box on an Access form when the form is opened? I have several forms that are used for both data entry and viewing. We use the search box a LOT. If someone types something to search, forgetting to put the cursor in the search box, they end up typing over data somewhere.
Would be awesome to put the cursor in the search box on FormOpen...but I haven't found out how, or if it's... -
Good, because it looks like I may be missing an extra 0 on the end myself, to complete the Nz command. Thanks twinnyfo!Leave a comment:
-
hjozinovis's response had a couple of errors (; vs. , and missing ")" etc). This worked for me. Note I changed to my control/form names.
Code:=Nz(IIf([subrptShopOrderTime].[Report].[HasData],[subrptShopOrderTime].[Report]![SOHours],0))
Leave a comment:
-
Ok guys...I'm about to display my inner genius!! I avoid code when possible because I'm not independent with it. But I still came up with a working solution. Since the one control would not hold code for all 20 of my fields, I split it up into two controls. I added up 10 in one control, and 10 in the other. I made those "not visible." Then, for my visible control, I added up the two new middleman controls. BOOM!!! SOLVED! LOL!...Leave a comment:
No activity results to display
Leave a comment: