How can I Sum() a text box?

  • Thread starter Thread starter M S
  • Start date Start date
M

M S

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
 
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.
 
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?

Pat Hartman said:
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.

M S said:
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
Click to expand...
Click to expand...
 
See my other response below. I can't have an aggregate function on the
report...?


Ken Hudson said:
I would think that:

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

would work?

M S said:
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
Click to expand...
Click to expand...
 
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?

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


Ken Hudson said:
I would think that:

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

would work?

M S said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.
M S said:
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?

Pat Hartman said:
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.

M S said:
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
Click to expand...
text
and
not a number??

How can I accomplish what I want to do?

Thanks in advance!!
-Mike
Click to expand...
Click to expand...
Click to expand...
 
Back
Top