dates and currency

[Elainie]

I have a table and a form. I have a weekly cost field and a monthly
cost field and a
projected total field. I have got the monthly cost field to add up the
weekly cost field and now would like the projected total field to add
up the monthly cost field between 2 date fields, the effective start
date and the end date.

Don't know where to start

HELP!

Elaine

[Nick 'The database Guy']

Re: dates and currency

Hi Elainie,

It strikes me that you want a totals query that will have critera on
the date.

The sql would look something like this.

SELECT Sum(MonthyCost) AS Total
FROM tblCosts
WHERE ExpenseDate [StartDate] and ExpenseDate < [EndDate]

Good luck

Nick


Elainie wrote:

I have a table and a form. I have a weekly cost field and a monthly
cost field and a
projected total field. I have got the monthly cost field to add up the
weekly cost field and now would like the projected total field to add
up the monthly cost field between 2 date fields, the effective start
date and the end date.
>
Don't know where to start
>
HELP!
>
Elaine

[ManningFan]

Re: dates and currency

So your Monthly Cost field knows which weeks are attributed to your
month? Are you doing this based on dates? Where are the dates stored?
Is your table flat, or do you have multiple relational tables? If
you're not doing this based on dates, are you multiplying the Weekly
Cost field by 4 to assume a Monthly Cost?

I'd say you can enter a start date and end date, use the Week()
function to find out which week number it is (between 1 and 52) and
then subtract it from the end date, but that's not going to work if
your project spans from, say, December 10th to January 20th. You're
probably going to have to do a DateDiff to find out how many days are
between your start and end dates, and then divide that by 7 to get the
number of weeks, and then multiply that by your weekly cost.

Elainie wrote:

I have a table and a form. I have a weekly cost field and a monthly
cost field and a
projected total field. I have got the monthly cost field to add up the
weekly cost field and now would like the projected total field to add
up the monthly cost field between 2 date fields, the effective start
date and the end date.
>
Don't know where to start
>
HELP!
>
Elaine