Find dates

[Kila]

I am working on a database tracking resident & student rotations in a hospital. Most of them rotate even amounts of time...from the 1st of a given month to the last of that month. There are fields in the database for Rotation Start date AND Rotation End Date. The problem is that some of the students rotate on six week schedules...ex. this month...6/14/04 - 7/25/04, and 7/26/04 - 9/2/04. We print a monthly roster showing everyone rotating in at any time during a given month. As you can see, July is no problem...All of the students either start or end during July. My current query searches for a start date OR and end date during July.

The problem at hand is August, or months like it, that fall between start & end dates in other months. (7/26/04 - 9/2/04). I need these students to show up on the August schedule.

How can I run a query that looks at the start & end dates of the rotation & returns a list of records that match for any date that falls in the time period I desire (say, August, or a particular week in August if I needed that.)

Thanks in advance for your help!

 

[FoFa]

How about
([BegDate] >= #09/01/2004# AND [BegDate] <= #09/31/2004#) AND [EndDate] >= [BegDate]

 

[namliam]

Brain stil farting Fofa?!

([BegDate] >= #09/01/2004# AND [BegDate] <= #09/31/2004#)

This will get all registriations for september, nothing more....

([BegDate] <= #09/01/2004# AND [EndDate] >= #09/01/2004#) or
([BegDate] <= #09/31/2004# AND [EndDate] >= #09/31/2004#)

That will do the trick i think....

Greetz

 

[Kila]

Thanks!!!!!!!

Hmmmmmm. Close I think, although, I think to capture those folks who start or end DURING September too, we need to tweak it like this...

([BegDate] >= #09/01/2004# AND [BegDate] <= #09/30/2004#)
(Residents who START their rotation during September)

OR​

([EndDate] >= #09/01/2004# AND [BegDate] <= #09/30/2004#)
(Residents who END their rotation during September)

OR​

([BegDate] < #09/1/2004# AND [EndDate] > #09/30/2004#)
(Residents who start their rotation BEFORE September AND end AFTER September.)

If I put the search date range in a dialog box, then I can use a parameter query that will put those dates for any date range where the September stuff is.

Thanks guys! I KNEW it was something simple (wordy, but simple). I just needed someone to point me in the right direction!