Calculate by months

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

re: Calculate by months

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" <ashfield@paradise.net.nzwrote in message
news:1178447521.623043.97710@u30g2000hsc.googlegro ups.com...

re: Calculate by months

Chesne <ashfield@paradise.net.nzwrote in
news:1178447521.623043.97710@u30g2000hsc.googlegro ups.com:
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

re: Calculate by months

On May 6, 5:32 am, Chesne <ashfi...@paradise.net.nzwrote: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
CDMAPoster@FortuneJames.com