Re: Sum of calculated fields

No, don't save the extra values in the table.

Since this seems to be in a report, I think you would be
better off using additional text boxes with their RunningSum
property instead of using Sum. For example, to get the
total overtime add a text box named txtRunOvertime with the
expression =[Overtime hours] and set its RunningSum proeprty
to OverAll.

Note that your calculation of midnight is overly complex.
It could just be DateValue(departtime +1)
--
Marsh
MVP [MS Access]


Chris B via AccessMonster.com wrote:
>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?
>
>
>>>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.
>>
>Marshall Barton wrote:
>>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.
.

Relevant Pages

  • Re: Summing unbound controls at several group levels, and grand to
    ... What I am trying to do is sum a value from multiple records, ... It would be nice to do this in one report. ... Once this calculation is performed, ...
    (microsoft.public.access.reports)
  • Re: Sum of calculated fields
    ... expressions I used so that it may make better sense, ... This database is used to calculate normal working hours and overtime hours, ... I have managed to get the sum() of Total hours to display in the report ...
    (microsoft.public.access.formscoding)
  • Re: Sum a time on a Rpt
    ... >i am trying that formula to sum all of the durations together in a report ... >What i did is i created a text box at the bottom of the report and plugged ... If the durations are in hours that should work, ... If the calculation is a simple expression, ...
    (microsoft.public.access.reports)
  • Re: Using Calculated Fields in Report Footers
    ... So, Access really can't sum a field created in the report, huh? ... One alternative is to perform the calculation in the ... The other is to use a report text box's RunningSum property. ...
    (microsoft.public.access.reports)
  • Re: Help with some form calculations
    ... because it works when all the controls have data but if of them does ... not have data then it wont display the sum. ... You can perform the calculation right on the report itself, ...
    (microsoft.public.access.forms)