Summing Fields

[andrewaldrich]

I have a subform that is continuous and linked to the master. The form contains a number of fields to calculate hours spent on a project. The fields I have are:

• StartTime - date/time field, short time
• EndTime - date/time field, short time
• HoursWorked - calculated field = [EndTime]-[StartTime] (I created the formula to return 0 if the [EndTime] is null and set the format property to fixed, 2 decimals)
• TotalHours - text field with the control property set to =Nz([HoursWorked])

In the form footer I have an unbound field with the control set to =Sum(Nz([TotalHours])). The field calculates as 0.

I had read where you cannot sum on a calculated field. I am assuming that since I set the TotalHours control property to the value of HoursWorked that this is the work around.

Please help.

Thanks

 

[Isskint]

There are a couple of work arounds.

Do a SUM() calculation on the same fields you use on each record, so Sum([EndTime]-[StartTime]) would give you sum hours worked. OR Sum(NZ([EndTime],0) -NZ([StartTime],0)) to allow for null

The other option is to create a query to supply the data for the form and calculate the hours worked in the query and you can then sum it on the form in the normal way.