How can I Sum() a text box?

[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

 

[Guest]

I would think that:

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

would work?

 

[Pat Hartman]

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]

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

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

 

[M S]

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

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

 

[Guest]

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

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

 

[Pat Hartman]

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

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

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