Chesne wrote:
I have a table with [BeginDate] and [NoOfNights] which are in an
accommodation db. I would like to be able to determine how many days
are occupied for a particular month using the two fields as per
above. By adding the [NoOfNights] to [BeginDate] tells me the date they
leave. However, for statistical purposes I need to calculate the number of
occupied days but when I reach closer to the end of the month some of
the "Enddate' are in the following month. Can anyone tell me how to
get around this one please? TIA
Here's an alternative method that will also work for cases (that you
probably don't have) of stays lasting for several months:
Public Function DateIntersection(dt1 As Date, dt2 As Date, dt3 As Date,
dt4 As Date) As Integer
'Return the number of days overlapping two date ranges
'Assumes d1 <= d2 and d3 <= d4 Ranges can go in either order
DateIntersection = 0
If dt2 <= dt3 Then
If dt2 = dt3 Then DateIntersection = 1
Exit Function
End If
If dt4 <= dt1 Then
If dt4 = dt1 Then DateIntersection = 1
Exit Function
End If
If dt1 <= dt3 And dt3 <= dt2 And dt2 <= dt4 Then
DateIntersection = DateDiff("d", dt3, dt2) + 1
Exit Function
End If
If dt1 <= dt3 And dt3 <= dt4 And dt4 <= dt2 Then
DateIntersection = DateDiff("d", dt3, dt4) + 1
Exit Function
End If
If dt3 <= dt1 And dt1 <= dt2 And dt2 <= dt4 Then
DateIntersection = DateDiff("d", dt1, dt2) + 1
Exit Function
End If
If dt3 <= dt1 And dt1 <= dt4 And dt4 <= dt2 Then
DateIntersection = DateDiff("d", dt1, dt4) + 1
End If
End Function
Usage:
intDaysStayedInMay = DateIntersection(#5/1/05#, #5/31/05#, dtStartDate,
dtEndDate)
or
SELECT BookingID, DateIntersection(#5/1/05#, #5/31/05#, [StartDate],
[EndDate]) AS DaysStayedInMay FROM tblBookings;
Note: DateSerial is the preferred way to obtain #5/1/05# and #5/31/05#.
James A. Fortune