Overlapping dates (1 Viewer)

[pbaldy]

Perhaps I'm having a brain cramp, but I'm having trouble coming up with a query to return people who are sick or on vacation at any point during a selected date range. I've attached a demo with a sample of the table in it. The important fields are OffStartDate and OffEndDate.

I need to be able to specify a "from" and "to" date, and get any record where OffStartDate and OffEndDate overlap the selected dates. For me, it gets tricky when those dates are both outside the selected dates (start date before the from, end date after the to).

TIA

 

[EMP]

Try the query attached.

 

[pbaldy]

Thanks EMP. I'm not at work, but a quick test looks good. I'll check it better in the morning.

Thanks again!

 

[pbaldy]

I appreciate the help. I ended up with 3 different WHERE clauses, which all return the same records in my testing. Shadow's seems like the simplest approach. Hopefully I haven't missed something in the logic:

WHERE (((dbo_tblSickVac.OffStartDate)<=[Forms]![Form1]![txtToDate]) AND ((dbo_tblSickVac.OffEndDate)>=[Forms]![Form1]![txtFmDate]));

I'll be incorporating this into my application today.

Thanks again for the help!

 

[dsthomson]

I have a need to find overlapping date ranges and the date overlap submitted by EMP seems to work just fine.
I have a need to find the number of days that the ranges overlap. How could I query for that?

 

[dsthomson]

Re: Overlapping dates (days)

This works (adapted files from this thread).
Simple but I hope it helps someone.