Totals of several calculated fields / controls

[Guest]

I've looked at the suggestions for totals in Access Reports (using totals as
the search phrase) and didn't see anything that will really help.
I have a series of fields / controls that are calculated text boxes. The
basic calculation in each control is [County Rate] * [Amount Claimed] / 1000.
There six of these controls per record. I want to have a seventh field that
will sum the six calculated controls, that is
=Text31+Text33+Text35+Text37+Text39+Text41. I tried to group the six controls
together but the seventh control still returns a zero.
Suggestions?

 

[Allen Browne]

Two issues:
- nulls;
- calculated controls misunderstood.

To solve the nulls, use Nz():
=Nz([Text31],0) + Nz([Text33],0) + Nz(...

The clue that Access is understanding your calculations as text instead of
numbers would be if the field left-aligns like text. If so, typecast each
calculation, e.g.:
CCur(Nz([County Rate] * [Amount Claimed] / 1000,0))

More on this issue in article:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

If you want more info on the Nulls issue, see:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html

 

[Guest]

Thanks, Allen. I'm at home right now about to hit the sack but I'll try this
first thing in the morning. It usually is something relatively simple. Too
bad Access help doesn't.
--
I know enuff to be dangerous.

Two issues:
- nulls;
- calculated controls misunderstood.

To solve the nulls, use Nz():
=Nz([Text31],0) + Nz([Text33],0) + Nz(...

The clue that Access is understanding your calculations as text instead of
numbers would be if the field left-aligns like text. If so, typecast each
calculation, e.g.:
CCur(Nz([County Rate] * [Amount Claimed] / 1000,0))

More on this issue in article:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

If you want more info on the Nulls issue, see:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I've looked at the suggestions for totals in Access Reports (using totals
as
the search phrase) and didn't see anything that will really help.
I have a series of fields / controls that are calculated text boxes. The
basic calculation in each control is [County Rate] * [Amount Claimed] /
1000.
There six of these controls per record. I want to have a seventh field
that
will sum the six calculated controls, that is
=Text31+Text33+Text35+Text37+Text39+Text41. I tried to group the six
controls
together but the seventh control still returns a zero.
Suggestions?

 

[Guest]

Thank you, Allen. It worked as claimed. Especially after I remembered to
insert all of the brackets, parentheses, spaces, etc. Really nice bit of code
that I'll keep handy.
Thanks again.
--
I know enuff to be dangerous.

Two issues:
- nulls;
- calculated controls misunderstood.

To solve the nulls, use Nz():
=Nz([Text31],0) + Nz([Text33],0) + Nz(...

The clue that Access is understanding your calculations as text instead of
numbers would be if the field left-aligns like text. If so, typecast each
calculation, e.g.:
CCur(Nz([County Rate] * [Amount Claimed] / 1000,0))

More on this issue in article:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

If you want more info on the Nulls issue, see:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I've looked at the suggestions for totals in Access Reports (using totals
as
the search phrase) and didn't see anything that will really help.
I have a series of fields / controls that are calculated text boxes. The
basic calculation in each control is [County Rate] * [Amount Claimed] /
1000.
There six of these controls per record. I want to have a seventh field
that
will sum the six calculated controls, that is
=Text31+Text33+Text35+Text37+Text39+Text41. I tried to group the six
controls
together but the seventh control still returns a zero.
Suggestions?