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