access database query with dates

my table looks like this

put your date in place of dte in the following where clause for the query
Where ((dte >= start_date) AND (dte <= end_date))

BTW, so you can visualize that, here's what it looks like (found one of my old answers):

Existing recs:         From  -------------------------- To
Case 1    From -------------- To
Case 2                                                       From ----------- To
Case 3                                  From ------ To


  In terms of checking for over laps of existing start/end ranges.

Jim.

If you just want to check a single date is within the existing range then my simple WHERE clause is good enough.  (That is what you asked for).
The comment by JDettman is interesting.  It made me think that could cover a gap in your logic where the new holidays start before and end after the existing holidays - a case that comment from JDettman doesn't actually cover, it would be Case 4 in the diagram
Existing recs:         From  -------------------------- To
Case 1    From -------------- To
Case 2                                                       From ----------- To
Case 3                                  From ------ To
Case 4    From---------------------------------------------------------------------To

Using Jim's example above and example dates of 4 - 7 July, I use a syntax of:

WHERE [start date] <= #07/07/2013# AND [end date] >= #7/4/13#

if you compare the new leave start date (4 July) to all other leave end dates, you only want to include those records where the already saved [end date] >= the new leave start date.  Likewise, you only want those records where the new leave end date (7 July) is >= the other leave start dates.

This simple construct will identify all of the cases mentioned by Jim, plus the additional case (case 4) where the new leave starts before an existing leave and ends after an existing leave.

Existing recs:         From  -------------------------- To
Case 1    From -------------- To
Case 2                                                       From ----------- To
Case 3                                  From ------ To
Case 4    From ----------------------------------------------------  To

<< It made me think that could cover a gap in your logic where the new holidays start before and end after the existing holidays - a case that comment from JDettman doesn't actually cover, it would be Case 4 in the diagram>>

 No, the three checks alone work.

 The diagram I posted is a bit confusing.  Case #4 as you posted is actually case #2; the from/to your checking spans an existing to (end date).  If doesn't matter if it is prior to the from date or not.  Just the fact that it spans the to date is what is important.

  Case #1 is the same, but for the start date.   The from/to your checking spans the from (start date).

  The last check is if from/to range falls entirely within an existing from/to range.

  I used this logic for a campgroud reservation system I wrote about fifteen years ago.

Jim.

Actually, here's the sample DB.

Jim.
res.zip

Jim,

I concede that the way your criteria is written for Case #2 would encompass the records associated with Case #4.  But you have to admit that my WHERE clause is significantly simpler than yours!

<<But you have to admit that my WHERE clause is significantly simpler than yours! >>

I have already setup timeoff for 7/1 - 7/7.

Now I come back and request off  7/4 - 7/5

WHERE 7/1 (existing start) <= 7/5 AND  7/7 (existing end) >= 7/4

That works as I get a conflict.

Now let's try 6/29 - 7/2:

WHERE 7/1 (existing start) <= 6/29 AND  7/7 (existing end) >= 7/2

 Doesn't work.

Now let's try 7/4 - 7/15:

WHERE 7/1 (existing start) <= 7/4 AND  7/7 (existing end) >= 7/15

 Doesn't work.

 I can't tell you how many hours it took me to wrap my head around the checks required for this when I did the campground app.  The three checks are the minimum you can have to solve this.

 There is one optimization that can be made depending on what your trying to show, but it can't be used in every case.

Jim.

Jim, you used:

Now let's try 6/29 - 7/2:

WHERE 7/1 (existing start) <= 6/29 AND  7/7 (existing end) >= 7/2

but that is not the correct use of the new dates, the correct use would be:

WHERE 7/1 (existing start) <= 7/2 AND  7/7 (existing end) >= 6/29

which does work.  You used the right date set in the first example, but not in the 2nd.

Dale

I've got a similar application to your campground app, and spent many hours as well.  Lets look at all the possible combinations for a new start/end combination verses those dates that are already booked, there are six of them:

Existing recs:         From  -------------------------- To
Case 1    From -------------- To
Case 2                                                       From ----------- To
Case 3                                  From ------ To
Case 4    From ----------------------------------------------------  To
Case 5    From -- To
Case 6                                                                       From --- To

if you look closely, you will notice that in all of the potential conflicts (cases 1-4) the Case "FROM" date is less than (or =) the existing "To" date, and the Case "T0" date is greater than (or =) to the existing "FROM" date.

Try it, you will like it!

Dale

Your right, I flipped the dates, so let me try one more time:

a. I have already setup timeoff for 7/1 - 7/7.
b. you check is:

WHERE (existing start date) <= New END Date AND (existing end date) >= New START date

Now I come back and request off  7/4 - 7/5:

WHERE 7/1 (existing start) <= 7/5 (new end) AND  7/7 (existing end) >= 7/4 (new start)

That works as I get a conflict.

Now let's try 6/29 - 7/2:

WHERE 7/1 (existing start) <= 7/2 (new end) AND  7/7 (existing end) >= 6/29 (new start)

 So yes that does work.

Now let's try 7/4 - 7/15:

WHERE 7/1 (existing start) <= 7/15 (new end) AND  7/7 (existing end) >= 7/4 (new start)

So yes again, that does work and HAT's OFF TO YOU!

  Your the first person in 15 years that has managed to find a more optimal solution to this (and several have tried everytime this comes up).

  In fact I'm a bit dumbfounded.   However, looking back at the chart, it's a simple matter of attacking it from the other direction:

Existing recs:         From  -------------------------- To
Case 1    From -------------- To
Case 2                                                       From ----------- To
Case 3                                  From ------ To

 
Rather then writing a check for each case # and comparing it to the existing recs (which is what I did), you wrote the check from the opposite viewpoint.

It's clear from the chart that in all cases, the existing from must be <= the new to and the existing to must be >= the new from for there to be a conflict.  Your statement does cover all three cases.

 I'm not sure why no one has ever spotted that before and my gut is telling me their is a catch some where, but it sure works logically.

Jim.

I am glad that I was able to contribute to your knowledge base today.

Have a good one!

Dale

>> I'm not sure why no one has ever spotted that before and my gut is telling me their is a catch some where, but it sure works logically.

I've used that logic in the past.  The only 'problem' I know of is that you must check the new start/end dates are normalised (and swap if necessary) - not that the user enters a new start date later than the new end date.


ps.  Jim, apologies for not checking the rather complex where clause initially, I based my criticism solely on the diagram.

Hi

Thanks to everyone for the comments and time, Jim's answer is the most comprehensive, and thanks for the example file too!

Just bear in mind for future questions

<<that comment doesn't actually answer your question - you specifically asked for checking if a single date falls within a range.>>

  That's not the way I read it.

<<As appeared later there is a much simpler solution should you require to check a range intersects with another range>>

  While Dale's solution is much more efficent then mine, mine still works.

  The only thing I would say to cycledude is to be aware that you can accept multiple comments as answer and personally, I think Dale should have gotten at least an assist if not the solution.  

<<ps.  Jim, apologies for not checking the rather complex where clause initially, I based my criticism solely on the diagram. >>

  No harm, no foul.   As I said, the diagram really wasn't all that clear in what it needed to show.  It was the overlaps that were important, not the spans themselves.

  Nothing wrong though with commenting about something you felt was wrong.   That's why EE is a great place to hangout; you get different types of thinking and viewpoints.   Dale's solution is case in point.

  Love learning something new or a better way to do things.

Jim.

Just to be pedantic:

How can I write a query that checks if a date I am passing in falls between the start and end dates that are already in the database?

date - singular.  (It might be that every single date in the range requires checking against something - public holidays...)

<<date - singular.  (It might be that every single date in the range requires checking against something - public holidays...) >>

 Right, but I think that was a mistake and he meant to say "dates".

at the start of the question he said:

"In a table I am storing the startdate and enddate of the holiday period, along with the employeeid and numberof days etc etc."

 and

"When I add a new entry, I need to check if any employee already has time off in this range, "

 If he adds a new entry, then he has a start and a end date to check within the existing ranges.

 So I took it as a range searching within existing ranges and he made a mistake by not saying "dates" later in the question.

Jim.

hi fyed! i was led here by Jim who answered my other question.

i just wanna say, fantastic solution!! and thanks for sharing it with all of us!! = ))