Calculate by Date (non date fields)

[turtle]

I am calculating actual costs against a budget and have stumbled upon
a problem. For estimating each months hours i have setup text fields
for our Program Managers(PM) to put in approximate hours to complete a
task. So I have a fields called Jan; Feb; Mar; .... These fields are
a bucket to hold estimated hours.
But if I want to compare the actuals to the budget as of this month
how do i do that. So since it is November right now I want to compare
actuals from Jan-Nov and also add up the totals of estimates from
Jan-Nov. However when December rolls around I want it to
automatically calculate from Jan - Dec.

Any ideas?
Thanks
KO

[Salad]

turtle wrote:

I am calculating actual costs against a budget and have stumbled upon
a problem. For estimating each months hours i have setup text fields
for our Program Managers(PM) to put in approximate hours to complete a
task. So I have a fields called Jan; Feb; Mar; .... These fields are
a bucket to hold estimated hours.
But if I want to compare the actuals to the budget as of this month
how do i do that. So since it is November right now I want to compare
actuals from Jan-Nov and also add up the totals of estimates from
Jan-Nov. However when December rolls around I want it to
automatically calculate from Jan - Dec.

Any ideas?
Thanks
KO

You could create a function. Pass the ID and month of the function. Ex:

Function SumBudget(lngID As Long, intMonth As integer) As Long
Dim i As Integer
Dim s As String
DIm rst as recordset
set rst = currentdb.openrecordset("Table"...)
rst.findfirst "Id = " & lngID

For i = 1 to intMonth
'get the 3 char abbreviation for the month
s = format(dateserial(Year(date()),i,1),"mmm")
SumBudget = SumBudget + NZ(rst(s),0)
next i
end function

This does not do any checking for years etc. But in a query you could
call this function (modified to your needs) in a column.
BudgetHours : SumBudget([ID],[MonthToPass])