Expression in Report

[Laura]

I need to write an expression for a report that will use
the "HoursWorked" field. If the HoursWorked are equal or
less than 32, then I would like the hours to be
multiplied by 12. If the Hours Worked are over 32, then
32 hours should be calculated by 12 and the hours over 32
should be put in a new field called Carryover. Hours are
entered through timesheet forms and the report I'm
creating adds the total hours for a specified time
period, i.e., =sum([hoursworked]). The record source is
a query that brings up three fields: townhome number,
hours worked and dates. The criteria for date is "Between
[enter state date] and [enter end date]. The report
prints one page per townhome. The =sum([hoursworked])
section adds up the hours worked for the specified dates.

The way this report works is that people work hours each
month, and can be credited for a maximum of 32 hours per
month. So any hours over 32 in that month need to go
into carryover. And if on a future month they work less
than 32 hours and have some time in carryover, then the
carryover should be used (up to a maximum of 32 hours of
carryover and actual hours for the month).

People are able to get a $12 per hour credit for
community service performed to a maximum of 32 hours per
month and this report is to show the status on the
monetary value of the hours worked during the month, and
also how many carryover hours they have. Any community
service they perform over 32 hours will go into a
carryover field. If they ever go under 32 hours in a
month, then carryover hours should be added to the actual
hours, up to a maximum of 32 hours per month. So the
carryover field will be constantly changing, either being
added to or subtracted from, depending on how many actual
hours are worked monthly.

How would I create a second calculated field to store the
carryover number? And is this the correct way to handle
it?

Any help is much appreciated.

 

[Marshall Barton]

I need to write an expression for a report that will use
the "HoursWorked" field. If the HoursWorked are equal or
less than 32, then I would like the hours to be
multiplied by 12. If the Hours Worked are over 32, then
32 hours should be calculated by 12 and the hours over 32
should be put in a new field called Carryover. Hours are
entered through timesheet forms and the report I'm
creating adds the total hours for a specified time
period, i.e., =sum([hoursworked]). The record source is
a query that brings up three fields: townhome number,
hours worked and dates. The criteria for date is "Between
[enter state date] and [enter end date]. The report
prints one page per townhome. The =sum([hoursworked])
section adds up the hours worked for the specified dates.

The way this report works is that people work hours each
month, and can be credited for a maximum of 32 hours per
month. So any hours over 32 in that month need to go
into carryover. And if on a future month they work less
than 32 hours and have some time in carryover, then the
carryover should be used (up to a maximum of 32 hours of
carryover and actual hours for the month).

People are able to get a $12 per hour credit for
community service performed to a maximum of 32 hours per
month and this report is to show the status on the
monetary value of the hours worked during the month, and
also how many carryover hours they have. Any community
service they perform over 32 hours will go into a
carryover field. If they ever go under 32 hours in a
month, then carryover hours should be added to the actual
hours, up to a maximum of 32 hours per month. So the
carryover field will be constantly changing, either being
added to or subtracted from, depending on how many actual
hours are worked monthly.

How would I create a second calculated field to store the
carryover number? And is this the correct way to handle
it?

Ah ha, I think that helps explain what you were asking in
all those other posts.

If the text box with the total hours worked is named
txtTotalHours, then i think you can calculate the amount of
credit with this expression in another text box:
=12 * IIf(txtTotalHours <= 32, txtTotalHours, 32)

and the amount of carryover text box would be:
=txtTotalHours - 32

Things will get a lot more complicated if you want to apply
the carryover from previous months to the current month
since the report doesn't see that data.