Working with date ranges

[Acegundam]

I am having an issue calling date ranges. The main purpose of my database is to keep track of all time off requests from my employees. Our weeks run Sunday through Saturday. So I have to two fields and the first field is start date and the second field is end date.

Here's my problem:

Jim Jones requested 11/4/2011 - 11/6/2011 off. (11/4/2011 is in field 1 and 11/6/2011 is in field 2)

So I want to call everyone who has time off between 11/6/2011 - 11/12/2011 to see who has requested time off for next week. What would be the best way of doing this?

I tried putting in Between #11/6/2011# And #11/12/2011# but because Mr Jone's start date is before 11/6/2011 his record is not being called into the query. I'm trying to avoid putting in a start date that is before the beginning of the week because I don't want old records being pulled.

[pbaldy]

Does this help?

http://www.baldyweb.com/OverLap.htm

[Acegundam]

That is exactly what I need, but I don't have much experience in access so where would I put that SQL string?

[Acegundam]

Ok, so I learned that the only line I need to edit is WHERE OffStartDate <= Forms!FormName.txtEndDate And OffEndDate >= Forms!FormName.txtStartDate

Here is the rest of the SQL strings I have in my query, if that helps SELECT [Time Off fields].[Last Name], [Time Off fields].[First Name], [Time Off fields].[Start Date], [Time Off fields].[End Date], [Time Off fields].Reason
FROM [Time Off fields]
WHERE ((([Time Off fields].[End Date]) Between #11/6/2011# And #11/12/2011#));