Re: Sum of calculated fields


Hi Marshal.,

Thank you for your assistance, sorry if I repeat this but ive include the
expressions I used so that it may make better sense, appologies for long
windyness!!
Thanks to others in this forum, I got this far....

This database is used to calculate normal working hours and overtime hours,
then multiply the ovetime hours by a rate which the user enters to give the
overtime amount due.
Ie an employee could leave today at a time and return several days later at a
time, this calculated the "normal" hours being the depart date/time until
midnight of the depart date & the remaining hours are thus overtime hours.
I also calculate the total hours IE dateDiff of depart date/time and return
date/time.
I have an option box that when "ticked" causes an IIF function to make normal
time 0 and overtime = total time

I refer to MidNight as an ivisible text on the form ==DateSerial(Year(DateAdd
("d",1,[departtime])),Month(DateAdd("d",1,[departtime])),Day(DateAdd("d",1,
[departtime])))

Total hours ==DateDiff("n",[departtime],[returntime])/60

Hours Normaltime =IIf([Option28]=False,(IIf((DateDiff("n",[departtime],
[MidNight])/60)>[TotHrs],[TotHrs],DateDiff("n",[departtime],[MidNight])/60)),
0)

Overtime hours ==[TotHrs]-[hrswitincall]

Overtime money due=[hrsoutsidecall]*[rate].

The only enteries actually made which affect table level would be the
[departtime], [returntime], [option28](option of exclusivley overtime), and
the rate.
All other fields are calculated on the fly.

I have managed to get the sum() of Total hours to display in the report
footer, but the moment i place any Sums() of the other fields ther all
display as errors - including then the Sum() of Total hours.

If I have read your advice correctly, am I correct that you cannot sum fields
that are not "saved" at table level?
would this be on of those cases where I should be "saving" the result of the
calculated fields into the table?
Is there any other method to sum() the fields which are entirely based on
expressions and display at report level, other than to export to excell and
add them up there?

Thanks once again for any advice!!




Marshall Barton wrote:
>>I have a database in to which start date/time and end date/time is entered,
>>from this I have calculated fields which calculate the timeDiff, also another
>[quoted text clipped - 9 lines]
>>I have read other threads and have tried by changing the Running sum property
>>of my Sum text box to Over group and tried Over All, still no avail.
>
>You are not very clear about what you are doing here. You
>mention both a form footer and a report footer. While Sum
>works the same in both situations, the RunningSum property
>is only available in reports.
>
>We need to know the names of the record source fields, the
>calculated values and the related expression used to
>calculate each value.
>
>A rule you may (or may not) be violating is that the
>aggregate functions (Count, Sum, etc) only operate on fields
>in the form/report's record source table/query. They are
>not aware of controls within the form/report. I suspect
>that this is the cause of the #Error you are seeing.
>


--
Message posted via http://www.accessmonster.com
.