I need "Sum" Help

[Matt]

I have a subform that I am trying to Sum the Time Spent * Rate. I have a
field in the db called TimeSpent that stores the number of minutes worked.
I have another field called Rate, that keeps track of the Hourly Rate. In
the detail of my form I created a control named txtTotal. The value of this
control was set to "= ([TimeSpent]/60) * [Rate]". This value calculates
perfectly, but when I put another control in my form footer named
txtGrandTotal, and set its value to "=Sum([txtTotal])", all I get is #Error.
Everything I have read shows that this is the way to do it, but everything I
have tried just gives me #Error. Just to see if I could even get it to
return a value, I tried "=Sum([TimeSpent]) * Sum([Rate])" and that actually
worked (of course, the value returned doesn't help any). So I tried
"=Sum([TimeSpent] * [Rate])" and that returns #Error. The fact that
"=Sum([TimeSpent]) * Sum([Rate])" actually returns a value makes me think
that everything is referenced correctly, I just don't know why it won't work
when Summing a control. Any one have any ideas with what would be causing
this?

Thanks for any help,
Matt

 

[Gerald Stanley]

You cannot sum a calculated control. If the control on the
detail section is "= ([TimeSpent]/60) * [Rate]", then the
correct syntax for its sum in the footer should be
"= Sum(([TimeSpent]/60) * [Rate])"

Hope This Helps
Gerald Stanley MCSD

 

[Matt]

You cannot sum a calculated control. If the control on the

detail section is "= ([TimeSpent]/60) * [Rate]", then the
correct syntax for its sum in the footer should be
"= Sum(([TimeSpent]/60) * [Rate])"

That's what I originally thought, but when looking through google, everyone
was saying that an expression in the footer couldn't be calculated, it
needed to be a control. I have tried the
"=Sum(([TimeSpent]/60) * [Rate])" and I still get the #Error. Even
"=Sum([TimeSpent] * [Rate])" gives an error. I was unable to get anything
to work when there was a calculation inside the Sum function. However
"=Sum([TimeSpent])" would work.

After playing with it for a few hours, I tried putting the Total calculation
in a view, and calling it on the form directly from the Expression Name in
the view and it worked. This will work fine for me, but I do have one other
question. When I apply a filter on the subform, the Sum field in the footer
doesn't change. Is there a way to update the sum field so the when I change
to say, all billable items, that the sum shows the sum of all records shown
(filtered) and not all records.

Thanks for the quick reply,
Matt

 

[Gerald Stanley]

Matt

I am using Access 2003 and the answer I gave below works
for me. Given the number of times you have changed the
controlSource of the control in the footer, it may be worth
deleting it and starting afresh with a new text box control.
With regards to your question on filtering, I would point
you in the direction of the DSum function.

Hope This Helps
Gerald Stanley MCSD

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

You cannot sum a calculated control. If the control on the
detail section is "= ([TimeSpent]/60) * [Rate]", then the
correct syntax for its sum in the footer should be
"= Sum(([TimeSpent]/60) * [Rate])"

That's what I originally thought, but when looking through google, everyone
was saying that an expression in the footer couldn't be calculated, it
needed to be a control. I have tried the
"=Sum(([TimeSpent]/60) * [Rate])" and I still get the #Error. Even
"=Sum([TimeSpent] * [Rate])" gives an error. I was unable to get anything
to work when there was a calculation inside the Sum function. However
"=Sum([TimeSpent])" would work.

After playing with it for a few hours, I tried putting the Total calculation
in a view, and calling it on the form directly from the Expression Name in
the view and it worked. This will work fine for me, but I do have one other
question. When I apply a filter on the subform, the Sum field in the footer
doesn't change. Is there a way to update the sum field so the when I change
to say, all billable items, that the sum shows the sum of all records shown
(filtered) and not all records.

Thanks for the quick reply,
Matt


.