How can I Sum() a text box?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

M S - 30 Mar 2005 21:34 GMT

I have a report with 10 different fields named "1" through "10" in the
detail section Then I have a text box (txtSkillAvg1) in the next footer
that has =Avg([1]*[Weight]/100) for a control source, that calculates the
weight this field carries in relation to the scores.... so far so good.

Now, I want in the page footer to have another text box which has
=Sum([txtSkillAvg1]), but this won't work.. because txtSkillAvg1 is text and
not a number??

How can I accomplish what I want to do?

Thanks in advance!!
-Mike

Reply to this Message

Ken Hudson - 30 Mar 2005 21:51 GMT

I would think that:

=Sum(Avg([1]*[Weight]/100))

would work?

> I have a report with 10 different fields named "1" through "10" in the
> detail section Then I have a text box (txtSkillAvg1) in the next footer
[quoted text clipped - 9 lines]
> Thanks in advance!!
> -Mike

Reply to this Message

M S - 31 Mar 2005 15:31 GMT

See my other response below. I can't have an aggregate function on the
report...?

> I would think that:
>
[quoted text clipped - 15 lines]
> > Thanks in advance!!
> > -Mike

Reply to this Message

Ken Hudson - 31 Mar 2005 17:37 GMT

Hi Mike,
Don't know whether your report is based on a table directly or a query. Can
you base it on a query and then include in the query the calculated average
fields that you want to carry into your report?

> See my other response below. I can't have an aggregate function on the
> report...?
[quoted text clipped - 21 lines]
> > > Thanks in advance!!
> > > -Mike

Reply to this Message

Pat Hartman - 30 Mar 2005 21:54 GMT

It doesn't work because Access can't sum a control. It can only sum a
field. To make this work, you need to repeat the calculation.
=Sum(Avg([1]*[Weight]/100))

BTW, it is poor practice to use numbers as column names. If you make a
mistake and forget to enclose the number in the square brackets, the number
will be used instead of your field and you won't get any error. Other
common poor practices - embedded spaces, special characters, property names,
function names, other reserved words.

Reply to this Message

M S - 31 Mar 2005 13:56 GMT

Thanks for the help....

I thought of that too... but then when I try that, I get

"Can't have aggregate function in expression (Sum(Avg([1]*[Weight]/100)))"

Any ideas on how to get around this?

> It doesn't work because Access can't sum a control. It can only sum a
> field. To make this work, you need to repeat the calculation.
[quoted text clipped - 20 lines]
> > Thanks in advance!!
> > -Mike

Reply to this Message

Pat Hartman - 31 Mar 2005 19:41 GMT

I should have been more specific - Access can't aggregate a control. It can
however, use it in a calculation. So you can have txtMySum with a
controlSource of
=Sum(Avg([1]*[Weight]/100))
You can then create an expression such as:
=[txtMySum]/2

This expression refers to the value of txtMySum for the CURRENT record only.
The reason you can't use aggregate functions on controls is that Access
doesn't keep all their values in memory. Only the value in the current
record is available.

> Thanks for the help....
>
[quoted text clipped - 32 lines]
> > > Thanks in advance!!
> > > -Mike

Reply to this Message