473,326 Members | 2,114 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Overlapping months

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

Nov 13 '05 #1
2 1555
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" <as******@paradise.net.nz> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
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

Nov 13 '05 #2
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

Nov 13 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: km | last post by:
Hi all, python re module deals with only nonoverlapping matches. how to go for if i want to find out overlapping matches and their span ? regards, KM
1
by: André Søreng | last post by:
With the re/sre module included with Python 2.4: pattern = "(?P<id1>avi)|(?P<id2>avi|mp3)" string2match = "some string with avi in it" matches = re.finditer(pattern, string2match) .......
11
by: Max M | last post by:
I am writing a "find-free-time" function for a calendar. There are a lot of time spans with start end times, some overlapping, some not. To find the free time spans, I first need to convert the...
3
by: Phil Sandler | last post by:
All, I have a table with start and end dates/times in it, and would like to be able to calculate the number of hours represented, accounting for overlapping records. Note that I am looking...
4
by: Charlie Brown | last post by:
I have a form with 2 custom controls that can be dragged around by a user. How can I check if they overlap each other without performing some kind of Collision detection on them? Is there...
4
by: =?ISO-8859-15?Q?Jean=2DFran=E7ois?= Lemaire | last post by:
Hello all, I'm learning C and I still am struggling to understand some basic concepts. For example, I read in the standard that with functions such as strcpy, 'If copying takes place between...
2
by: Durand | last post by:
I got a really annoying problem with datetime graphs. The problem is that with a long range time graph, the text on the x axis keeps overlapping like here:...
0
by: richard12345 | last post by:
Hi Guys I have problem with site I am building. The sidebar with menu and other thinks is overlapping footer. The footer move with the content and but it dos it dos not move with the sidebar. ...
3
by: cowboyrocks2009 | last post by:
Hi, I am trying to write a Java program to plot rectangles with different colors side by side non overlapping but unfortunately I am unable to do that as of now. Suppose I want to create 3...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.