Tme Sensitive Date Calculation

[Rose]

Currently I have a query that states Nights:[CheckOutDate]-[CheckInDate].
The problem is that I am putting a parameter of Between two dates in the
checkin field.

Nights: IIf(DateDiff("d",[CheckInDate],IIf([CheckOutDate] Is
Null,Date(),[CheckOutDate]))=0,1,DateDiff("d",[CheckInDate],IIf([CheckOutDate] Is Null,Date(),[CheckOutDate])))

My challenge is if I input Between 09/01/2006 and 09/30/2006. How can I
only count the days in September and not those dates that may have happend
prior for example if people checked in on August 28 and Check out on
September 3. I would only want the query to count the two nights in
September. Also if they
checked in on September 29 and Checked Out on October 4th I would only want
the days in September. Any Ideas.

Check In Date Check Out Date Nights
09/01/2006 09/05/2006 4
09/10/2006 09/11/2006 1
09/12/2006

 

[Marshall Barton]

Currently I have a query that states Nights:[CheckOutDate]-[CheckInDate].
The problem is that I am putting a parameter of Between two dates in the
checkin field.

Nights: IIf(DateDiff("d",[CheckInDate],IIf([CheckOutDate] Is
Null,Date(),[CheckOutDate]))=0,1,DateDiff("d",[CheckInDate],IIf([CheckOutDate] Is Null,Date(),[CheckOutDate])))

My challenge is if I input Between 09/01/2006 and 09/30/2006. How can I
only count the days in September and not those dates that may have happend
prior for example if people checked in on August 28 and Check out on
September 3. I would only want the query to count the two nights in
September. Also if they
checked in on September 29 and Checked Out on October 4th I would only want
the days in September. Any Ideas.

Check In Date Check Out Date Nights
09/01/2006 09/05/2006 4
09/10/2006 09/11/2006 1
09/12/2006

No promises about all the parenthesisand commas being
exactly right, but try something like:

Nights: IIf(DateDiff("d", IIf(CheckInDate < [Start Date],
[Start Date], IIf(Nz(CheckOutDate, Date()) > [End Date],
[End Date], Nz(CheckOutDate, Date()))) = 0, 1),
DateDiff("d", IIf(CheckInDate < [Start Date], [Start Date],
IIf(Nz(CheckOutDate, Date()) > [End Date], [End Date],
Nz(CheckOutDate, Date()))