Calculated control

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to be able to total the # of hours volunteers worked by month,
including any "carry over" hours from last year. I've inserted a calculated
control with the expression =[Carry_OverHours]+[JAN06]+[FEB06]+[MAR06] etc.,
the same as I did last year for 2005 data. It worked just fine last year,
but is not working this year! Can anyone help me out? Many thanks!
 
I need to be able to total the # of hours volunteers worked by month,
including any "carry over" hours from last year. I've inserted a calculated
control with the expression =[Carry_OverHours]+[JAN06]+[FEB06]+[MAR06] etc.,
the same as I did last year for 2005 data. It worked just fine last year,
but is not working this year! Can anyone help me out? Many thanks!
Click to expand...

Might there be a NULL among your fields? Anything plus NULL is NULL.

Try

NZ([CarryOverHours]) + NZ([JAN06]) + NZ([FEB06]) + ...

Note that your table structure *is completely incorrectly normalized*;
storing data (dates) in fieldnames is spreadsheet logic, and is
emphaticaly *not* appropriate for a relational database. You'll be
much better off with a "tall thin" table structure with fields like

VolunteerID
WorkDate Date/Time ' just store the first of the month, 1/1/06
HoursWorked

If a volunteer worked every month in 2005 and in January 2006, there'd
be 13 records in the table; and a Totals query will sum the hours
without any carryover field or such problems.

John W. Vinson[MVP]
 
Back
Top