Calculate unbound textbox

[Guest]

Hi,

I have created a report to calculate hours worked per week. This works
fine, I have added textboxes that need to calculate
1. How many hours have been worked over 40 hours but less than 50 hours.
2. Another text box to calculate how many hours worked over 50 hours.

there are a further 3 more textboxes which are total figure calculations for
each of the above ie.
1. Overall Total hours
2. Overall total hours over 40 hours
3. Overall total hours over 50 hours.

Is this possible?

Thanks

 

[Allen Browne]

You already have a text box in the Report Footer section.
Let's assume it has these properties:
Control Source =Sum([Hours])
Format General Number
Name txtTotalHours

Your second text box will then have a Control Source of:
=IIf([TotalHours] <= 40, 0, IIf([TotalHours] < 50, 50 - [TotalHours],
10))

And the third one:
=IIf([TotalHours] > 50, [TotalHours] - 50, 0)

 

[Guest]

It seems from your question you are trying to do two levels of aggregate
functions. The "overall" totals (the 3 additional calculations you want to
add) seem to be for your whole report. What is the grouping for your 3
initial calculations?
Do you have grouping levels in your report, such as by employee ID or by
date (weekly)?

Let's assume you are using the 3 text boxes with 2 text boxes having
controlsource as Allen described in a detail section instead of in your
report footer section: txtTotalHours, txtHoursOver40, and txtHoursOver50.
(I'm not sure what the controlsource of your txtTotalHours would be. It
depends on how you are doing your initial calculations.)

In that same detail section, add 3 invisible text boxes:
SumRegularHours which has the same controlsource as txtTotalHours, and
SumHoursOver40 which has the same controlsource as txtHoursOver40, and
SumHoursOver50 which has the same controlsource as txtHoursOver50.

Set the RunningSum property on these textboxes to "Over All".

Then in your report footer, you can add 3 text boxes that will show your
"overall" calculations.

1. OverallTotalHours has Control Source =[SumRegularHours]
2. OverallTotalOver40 has Control Source =[SumHoursOver40]
3. OverallTotalOver50 has Control Source =[SumHoursOver50]

Hope this helps...


--------------- In reply to -------------------

You already have a text box in the Report Footer section.
Let's assume it has these properties:
Control Source =Sum([Hours])
Format General Number
Name txtTotalHours

Your second text box will then have a Control Source of:
=IIf([TotalHours] <= 40, 0, IIf([TotalHours] < 50, 50 - [TotalHours],
10))

And the third one:
=IIf([TotalHours] > 50, [TotalHours] - 50, 0)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I have created a report to calculate hours worked per week. This works
fine, I have added textboxes that need to calculate
1. How many hours have been worked over 40 hours but less than 50 hours.
2. Another text box to calculate how many hours worked over 50 hours.

there are a further 3 more textboxes which are total figure calculations
for
each of the above ie.
1. Overall Total hours
2. Overall total hours over 40 hours
3. Overall total hours over 50 hours.

Is this possible?

Thanks