Sum of calculated fields

  • Thread starter Chris B via AccessMonster.com
  • Start date
C

Chris B via AccessMonster.com

Please help,

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
which calculates the time from a Midnight reference.
A rate is entered and this calculates the amount of money due.
This is all working perfectly, the problem is when I try to sum the
calculated fields, I keep getting #ERRORs,
I have tried to place the Sum() in both form footer as well as the report
footer, I too have tried by placing the complete expression into the Sum
(expression).
Where else can I put the Sum(), I dont mind if its only ever displayed once
the report is generated.
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.

Any suggestions would be much appreciated!
 
M

Marshall Barton

Chris said:
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
which calculates the time from a Midnight reference.
A rate is entered and this calculates the amount of money due.
This is all working perfectly, the problem is when I try to sum the
calculated fields, I keep getting #ERRORs,
I have tried to place the Sum() in both form footer as well as the report
footer, I too have tried by placing the complete expression into the Sum
(expression).
Where else can I put the Sum(), I dont mind if its only ever displayed once
the report is generated.
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.
 
C

Chris B via AccessMonster.com

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 said:
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.
 
M

Marshall Barton

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]

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 said:
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.
 
C

Chris B via AccessMonster.com

Hi Marshall,

Thanks so much for the advice,
I have changed Midnight to (DateAdd("d",1,[departtime])), I couldnt get the
DateValue expression to work.
I placed text boxes with parameters set as suggested into the report footer,
but get a 0 instead of the running sum.
Any ideas on this
Would it work if I placed the expressions into a query and sum the query?

Marshall said:
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)
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
[quoted text clipped - 58 lines]
 
M

Marshall Barton

Using DateAdd is more rigorous then just +1, but the
DateValue function is the one that strips away any time part
and makes it midnight:
Midnight = DateValue(DateAdd("d",1,[departtime]))

Getting a RunningSum total of zero indicates that the values
are not referenced correctly, Double check that all the
names are correct. Or maybe some records don't have a
value, try using the expression:
=Nz([Overtime hours], 0)
in the txtRunOvertime text box.

You probably could put together a query to do this, but that
would introduce a different set of complications. Once you
work out the details of using RunningSum text boxes, they
are pretty straightforward.
--
Marsh
MVP [MS Access]

I have changed Midnight to (DateAdd("d",1,[departtime])), I couldnt get the
DateValue expression to work.
I placed text boxes with parameters set as suggested into the report footer,
but get a 0 instead of the running sum.
Any ideas on this
Would it work if I placed the expressions into a query and sum the query?

Marshall said:
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)
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
[quoted text clipped - 58 lines]
not aware of controls within the form/report. I suspect
that this is the cause of the #Error you are seeing.
 
C

Chris B via AccessMonster.com

Marshall You are the greatest!!!!!
Got it right thanks to your help,
ended up using the following in the txtRunOvertime text box :
=Nz(Sum([Forms]![tblemployee]![tbldetails].[Form]![overtime]),0)
with the runtime property set to over all, when i tried to do it without
"sum", then running sum on its own wouldnt work, no idea why?. also used your
improved referance to MidNight.
Thank you once again!!!
ChrisB

Marshall said:
Using DateAdd is more rigorous then just +1, but the
DateValue function is the one that strips away any time part
and makes it midnight:
Midnight = DateValue(DateAdd("d",1,[departtime]))

Getting a RunningSum total of zero indicates that the values
are not referenced correctly, Double check that all the
names are correct. Or maybe some records don't have a
value, try using the expression:
=Nz([Overtime hours], 0)
in the txtRunOvertime text box.

You probably could put together a query to do this, but that
would introduce a different set of complications. Once you
work out the details of using RunningSum text boxes, they
are pretty straightforward.
I have changed Midnight to (DateAdd("d",1,[departtime])), I couldnt get the
DateValue expression to work.
[quoted text clipped - 19 lines]
 
M

Marshall Barton

Well, I'm glad you got it to work, but I have no idea what
that Sum is doing in there. I would have expected that to
either generate an error or cause the calculation to fly off
the rails. I probably still don't fully understand what you
want here, but regardless of my lingering confusion, you do
not need the [Forms]![tblemployee]![tbldetails].[Form]! part
of the expression, just =Nz(Sum([overtime]),0) should be
sufficient.
--
Marsh
MVP [MS Access]

Marshall You are the greatest!!!!!
Got it right thanks to your help,
ended up using the following in the txtRunOvertime text box :
=Nz(Sum([Forms]![tblemployee]![tbldetails].[Form]![overtime]),0)
with the runtime property set to over all, when i tried to do it without
"sum", then running sum on its own wouldnt work, no idea why?. also used your
improved referance to MidNight.
Thank you once again!!!
ChrisB

Marshall said:
Using DateAdd is more rigorous then just +1, but the
DateValue function is the one that strips away any time part
and makes it midnight:
Midnight = DateValue(DateAdd("d",1,[departtime]))

Getting a RunningSum total of zero indicates that the values
are not referenced correctly, Double check that all the
names are correct. Or maybe some records don't have a
value, try using the expression:
=Nz([Overtime hours], 0)
in the txtRunOvertime text box.

You probably could put together a query to do this, but that
would introduce a different set of complications. Once you
work out the details of using RunningSum text boxes, they
are pretty straightforward.
I have changed Midnight to (DateAdd("d",1,[departtime])), I couldnt get the
DateValue expression to work.
[quoted text clipped - 19 lines]
not aware of controls within the form/report. I suspect
that this is the cause of the #Error you are seeing.
 
C

Chris B via AccessMonster.com

Whoops!
I was a little premature in my excitement, after compacting and repairing it
bugged out as you predicted,
I reviewed all my expressions as you mention below, lost the "sum" and other
unnessasary parts of the expressions, cleaned up, and now it definately works
great!
Thanks once again!!!!

Marshall said:
Well, I'm glad you got it to work, but I have no idea what
that Sum is doing in there. I would have expected that to
either generate an error or cause the calculation to fly off
the rails. I probably still don't fully understand what you
want here, but regardless of my lingering confusion, you do
not need the [Forms]![tblemployee]![tbldetails].[Form]! part
of the expression, just =Nz(Sum([overtime]),0) should be
sufficient.
Marshall You are the greatest!!!!!
Got it right thanks to your help,
[quoted text clipped - 27 lines]
 
M

Marshall Barton

Great siiggghhh of relief.

Thanks for posting back with your final results, I was
concerned that I had misunderstood the problem and was
leading you astray. It's good to know that I was wrong when
I thought I might have made a mistake ;-)
--
Marsh
MVP [MS Access]

Whoops!
I was a little premature in my excitement, after compacting and repairing it
bugged out as you predicted,
I reviewed all my expressions as you mention below, lost the "sum" and other
unnessasary parts of the expressions, cleaned up, and now it definately works
great!
Thanks once again!!!!

Marshall said:
Well, I'm glad you got it to work, but I have no idea what
that Sum is doing in there. I would have expected that to
either generate an error or cause the calculation to fly off
the rails. I probably still don't fully understand what you
want here, but regardless of my lingering confusion, you do
not need the [Forms]![tblemployee]![tbldetails].[Form]! part
of the expression, just =Nz(Sum([overtime]),0) should be
sufficient.
Marshall You are the greatest!!!!!
Got it right thanks to your help,
[quoted text clipped - 27 lines]
not aware of controls within the form/report. I suspect
that this is the cause of the #Error you are seeing.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top