Overlapping months

[Chesne]

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

[Allen Browne]

The booking ends on:
DateAdd("d", [NoOfNights], [BeginDate])

The last day of the month is:
DateSerial(Year([BeginDate]), Month([BeginDate]) + 1, 0)

You want whichever is the lesser of those two, so:

IIf(DateAdd("d", [NoOfNights], [BeginDate]) <
DateSerial(Year([BeginDate]), Month([BeginDate]) + 1, 0),
DateAdd("d", [NoOfNights], [BeginDate]),
DateSerial(Year([BeginDate]), Month([BeginDate]) + 1, 0))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Chesne" <ashf...@paradise.net.nz> wrote in message
news:1110338672.7...@f14g2000cwb.googlegroups.com...

[jimfo...@compumarc.com]

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