Calculate by months

[Chesne]

Can anyone point me in the right direction please

I have a table with the following fields [BeginDate], Enddate], [Rate]
in dollars) and [NoOfNights] in an Accommodation db.

I would like to get a total for each month, however some of the start
and end dates of the bookings begin in one month and end in the
month(s) following. How do I sort out the problem

TIA

[Scott]

I think you need to create a table that updates when you use your table you
mention below, you need it to have each individual day and the rate listed
that way you could easily calculate out your monthly rates. You could have
these second table auto populate using code. I don't think there is anyway
to do it with your current set up, atleast not easily.

"Chesne" <as******@paradise.net.nzwrote in message
news:11*********************@u30g2000hsc.googlegro ups.com...

Can anyone point me in the right direction please

I have a table with the following fields [BeginDate], Enddate], [Rate]
in dollars) and [NoOfNights] in an Accommodation db.

I would like to get a total for each month, however some of the start
and end dates of the bookings begin in one month and end in the
month(s) following. How do I sort out the problem

TIA

[Bob Quintal]

Chesne <as******@paradise.net.nzwrote in
news:11*********************@u30g2000hsc.googlegro ups.com:

Can anyone point me in the right direction please

I have a table with the following fields [BeginDate],
Enddate], [Rate] in dollars) and [NoOfNights] in an
Accommodation db.

I would like to get a total for each month, however some of
the start and end dates of the bookings begin in one month and
end in the month(s) following. How do I sort out the problem

TIA

You can calculate the number of days from the start date to the
end of the month. You can also do the first of the (next) month
to the endDate.

This has a problem if the stay starts 31st of march and runs to
1st of may.
..
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[CDMAPoster]

On May 6, 5:32 am, Chesne <[e-mail]:

Can anyone point me in the right direction please

I have a table with the following fields [BeginDate], Enddate], [Rate]
in dollars) and [NoOfNights] in an Accommodation db.

I would like to get a total for each month, however some of the start
and end dates of the bookings begin in one month and end in the
month(s) following. How do I sort out the problem

TIA

I see that you've been working on this problem for over two years:

http://groups.google.com/group/comp....fcbcf1c83ddd11

I use the DateIntersection function to help me get started with the
logic for the SQL statement. Once the SQL statement is providing
correct answers it is often feasible to replace the DateIntersection
function with a subquery or a join thereby eliminating dependence on
VBA.

James A. Fortune
CD********@FortuneJames.com