By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,530 Members | 891 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,530 IT Pros & Developers. It's quick & easy.

Calculate by months

P: n/a
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

May 6 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

May 6 '07 #2

P: n/a
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

May 6 '07 #3

P: n/a
On May 6, 5:32 am, Chesne <ashfi...@paradise.net.nzwrote:
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

May 7 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.