Calculated Controls/Expressions

[Laura]

Hello - I need to write an expression that will use
the "HoursWorked" field. If the HoursWorked are 32 or
less then I would like the hours to be calculated 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.

I have never done this before, so could use help. Thank
you!

 

[Bruce M. Thompson]

Hello - I need to write an expression that will use

the "HoursWorked" field. If the HoursWorked are 32 or
less then I would like the hours to be calculated 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.

You need to be more clear in what you mean by "calculated by 12". Do you mean
that the HoursWorked should be divided by 12?

An expression won't assign a value to any field, so that latter part would need
to be done in code, or a second calculated field could be implemented to display
that value.

 

[Laura]

Hi Bruce - I'm sorry I was not more clear. It would be
multiplied by 12.

How would I create the second calculated field to store
the carryover number? The way this report works, is that
people work hours each month, and can only be credited
for 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). I hope I'm explaining this correctly.

Hours are entered through timesheets and the report I'm
creating adds the total hours for a specified time period
for: sum([hoursworked]).

If you have ideas on how I can get the report to work, it
would be much appreciated!! Thank you!

-----Original Message-----

You need to be more clear in what you mean

by "calculated by 12". Do you mean

 

[Bruce M. Thompson]

Hi Bruce - I'm sorry I was not more clear. It would be

multiplied by 12.

How would I create the second calculated field to store
the carryover number?

As I said in my earlier post, a calculated field *can't* save a value to a
field. You may have to actually store that value. It looks as though you might
need to store more than that value.

The way this report works, is that
people work hours each month, and can only be credited
for 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). I hope I'm explaining this correctly.

I can see that this could get a little complicated (and you would want to store
some values in this case), not in the calculations department, but in how you
would allocate the carryover hours in the future. Here's how I would break down
the hours using VBA:

'************EXAMPLE START
Dim iBaseHours as Integer
Dim iCarryOver as Integer

'Determine the allocation of hours
If [HoursWorked] > 32 then
iBaseHours = 32
iCarryOver = [HoursWorked]-32
Else
iBaseHours = [HoursWorked]
iCarryOver = 0
End If

'Perform your calculations
[HoursAllowedField] = iBaseHours * 12
[Carryover]= iCarryOver
'************EXAMPLE END

I don't know what the overall structure of your application and data are, so I
can't get too far into what to do with the values.

Hours are entered through timesheets and the report I'm
creating adds the total hours for a specified time period
for: sum([hoursworked]).

If summing that field, I would imagine that your "time periods" might need to be
per month, or grouped by month, anyway.

If you have ideas on how I can get the report to work, it
would be much appreciated!! Thank you!

Is this database for a non-profit organization? If so, and the database file(s)
are not too large, I might be willing to take a look at it. Let me know and I
may have you email a *zipped* copy to me by email (not to this newsgroup). You
may, in this case, want to contact me directly by email and we can see if this
scenario will work out. If you have (and know how to use) Winzip, or a similar
compression utility, try zipping your database (if split, include both the
front-end and the back-end MDBs) and get back to me with the resulting file
size.