Calculated field in group footer problem

[George Bowyer]

I have a report showing how many people are working at various tasks.

The report has a group header for [Date] and beneath that a group header for TaskID, which has the [fldTaskName] and [fldHours], which is the number of hours that each task takes.

The Detail section then contains the people involved.

In the TaskID footer, I have [txtPeople] which sums the number of people involved. I then have [txtManHours] which calculates txtPeople * fldHours.

This works fine.

However, I now want to have a calculated field in the Date footer = sum([txtManHours]), to show the total number of manhours per day.

When I try this I get "Enter parameter value txtManHours".

Where am I going wrong, please?

[Incidentally, there are some [taskID]s with a null date and 0 (Zero) hours entries - but I don't think that's relevant because I still get the problem when I exclude those taskIDs from the DataSource]

 

[trucktime]

Sum

I'm not sure at all about this but the problem may be that you are trying to sum a calculated field.

May be this will work:

Instead of: = sum([txtManHours])

put:

=Sum([txtPeople]) * Sum([fldHours])

 

[George Bowyer]

Summing the calculated field does seem to be where I'm coming adrift.

The problem with =Sum([txtPeople]) * Sum([fldHours]) is that it works out Sum([fldHours) as one lot of hours per record, whereas one record can in fact be equivalent to more than one worker.

In otherwords, there is a number field in the detail section which has the number of workers relevant to that record. These are all summed in the TaskID footer and then multiplied by fldHours to give fldManHours.

If I put [fldHours*number] in the detail section, then if I try to sum that in the TaskID footer I get the same enter parameter problem.

 

[George Bowyer]

Coming to think of it, =Sum([txtPeople]) * Sum([fldHours]) would not give the right answer any way.

If I had 1 person for 1 hour, 1 person for 2 hours and 1 person for three hours, the total hours worked is 6.

=Sum([txtPeople]) * Sum([fldHours]) would give 18.

 

[Rich]

=Sum(Iif[people]=1,1,[People])*[Hours])

 

[George Bowyer]

So I guess the question is, how can you, in one group footer, sum a field calculated in another?

 

[Sergeant]

I faced this once, and through this forum I learned to tap into the OnFormat events and store the calculated value in a public variable and then when I wanted to use it for a further calculation, it was right there waiting for me...I would just use the OnFormat event of the footer to build the value I needed and plug it in to a text box.

How's that for a run-on?

 

[George Bowyer]

I can't work put how to make that work yet.

However, I have overcome the problem another way.

I have a second copy of the txtManHours textbox in the taskID footer with it's running sum set to "Over Group".

A text box in the date footer just reads this figure.

I have another copy with running sum set to "Over All" feeding a summary in the report footer.

As with many of these things, it's taken me ages to work it out, but most of you are probably saying "Duh! Obvious...!"

It's easy when you know how...