Here is the scenario and for the life of me, I can't figure it out:
I have a report that I need to generate to determine the number of days stayed in a room.
I have a form that has two unbound text boxes (Start Date and End Date) that the user will fill in. The query I run pulls filtered data from the table with the Check-in Date field having the criteria of "Between [StartDate] and [EndDate]. The criteria does reference the right controls on the form. The proper syntax has been left out for simplicity.
Use this data as an example:
Record 1: Name: John Doe, Check-in Date: 1-5-03, Check-out Date: 1-10-03
Record 2: Name: Jane Doe, Check-in Date: 1-10-03, Check-out Date: 3-10-03
Record 3: Name: Jim Doe, Check-in Date: 2-12-03, Check-out Date: 2-14-03
Here is my dilema. I can calculate the total number of days stayed perfectly by subtracting the Check-In Date from the Check-out Date. What I need to be able to do is calculate the number of days stayed for each month prior if their stay overlaps a month or more. If I run a report to filter from 1-1-03 to 1-31-03, Record 1 and 2 are returned with the proper length of stay calculated. If I run a report from 2-1-03 to 2-28-03, only record 3 is returned. What I need it to do is return Record 2 as well in order to show that the person stayed 28 days in February and 21 days in January. I know why Record 2 isn't being returned. It's because the Check-in Date isn't between the Start Date and End Date criteria from the form.
My question is, how do I create the query or code for the report so that it knows to look for that second record and calculate the days stayed properly for both January and February?