Calculating Unbound Text Boxes on a Report

[sunshineleo]

Hi,
I have a report that pulls information from a Select Query. A form dictates
what information appears in the unbound text boxes (in the details section),
using

Month1.ControlSource = Forms![frm_EnterData]![txtMonth] + 0

I want to be able to calculate sums based on the unbound text boxes... can
this be done? I also want to sum these after each grouping.

Thanks for any guidance.
C

 

[sunshineleo]

Also, if there is no value in the text box (just nothing, not even a 0), the
row does not calculate correctly.

 

[sunshineleo]

OK. I've figured out the zero thing and calculating the rows.... with Val.
Now I just need to subtotal in the Country Footer because that's how the
report is grouped by.

Also, if there is no value in the text box (just nothing, not even a 0), the
row does not calculate correctly.

Hi,
I have a report that pulls information from a Select Query. A form dictates
what information appears in the unbound text boxes (in the details section),
using

Month1.ControlSource = Forms![frm_EnterData]![txtMonth] + 0

I want to be able to calculate sums based on the unbound text boxes... can
this be done? I also want to sum these after each grouping.

Thanks for any guidance.
C

 

[Marshall Barton]

OK. I've figured out the zero thing and calculating the rows.... with Val.
Now I just need to subtotal in the Country Footer because that's how the
report is grouped by.

Also, if there is no value in the text box (just nothing, not even a 0), the
row does not calculate correctly.

I have a report that pulls information from a Select Query. A form dictates
what information appears in the unbound text boxes (in the details section),
using

Month1.ControlSource = Forms![frm_EnterData]![txtMonth] + 0

I want to be able to calculate sums based on the unbound text boxes... can
this be done? I also want to sum these after each grouping.

Check into using the Nz function instead of Val.

I also think you probably should use the Value property
instead of the ControlSource property. The ControlSource is
supposed to be a field name or an expression that starts
with an = sign/
Month1.Value = Nz(Forms!frm_EnterData!txtMonth, 0)

To sum up the unbound values, add a (hidden?) text box named
txtRunTotal next to the Month1. Set its Control Source to
=Month1 and its RunningSum property to Over Group. Then a
group footer text box can display the group total by using
the expression txtRunTotal. A similar arrangement can be
used to get a grand total in the report footer.

 

[sunshineleo]

I used the NZ Statement in the Query, transform.
I tried to change

Month1.ControlSource = Forms![frm_EnterData]![txtMonth] + 0

to

Month1.Value = Nz(Forms!frm_EnterData!txtMonth, 0)

but it changes to

Month1.Value = Forms![frm_EnterData]![txtMonth] + 0

What's up with that? It's strange.

I have another question.... maybe I should start another thread....?

I have another report with set fields in the header with the amounts in the
details section... I want to be able to have the user pick the start quarter
for the report. How do I do this? It only shows the first quarter in the
report. Can something be done with the OnFormat? I need them to be able to
pick the start quarter and have the report show all the values for those
fields in that quarter, plus I need it to show a total for the previous
quarter. Do you need any more information?

I have the totals by quarter in a Select Query.

Thanks.

OK. I've figured out the zero thing and calculating the rows.... with Val.
Now I just need to subtotal in the Country Footer because that's how the
report is grouped by.

Also, if there is no value in the text box (just nothing, not even a 0), the
row does not calculate correctly.

:
I have a report that pulls information from a Select Query. A form dictates
what information appears in the unbound text boxes (in the details section),
using

Month1.ControlSource = Forms![frm_EnterData]![txtMonth] + 0

I want to be able to calculate sums based on the unbound text boxes... can
this be done? I also want to sum these after each grouping.

Check into using the Nz function instead of Val.

I also think you probably should use the Value property
instead of the ControlSource property. The ControlSource is
supposed to be a field name or an expression that starts
with an = sign/
Month1.Value = Nz(Forms!frm_EnterData!txtMonth, 0)

To sum up the unbound values, add a (hidden?) text box named
txtRunTotal next to the Month1. Set its Control Source to
=Month1 and its RunningSum property to Over Group. Then a
group footer text box can display the group total by using
the expression txtRunTotal. A similar arrangement can be
used to get a grand total in the report footer.

 

[sunshineleo]

I tried what you said but every time I execute the On Click, it reverts back
to the old code, but using Value instead of Control Source, the rest of the
code reverts back to the old way? What's it's problem?

Also, I have another report with set fields in the header with the values in
the details field.

Q2-2006 Field1 Field2 Field3 Field3 Q3-2006
Item
Item
Item
Total by group

There are 22 fields... I want to be able to have the user pick the quarter
they need, i.e. Q3-2006, which would fill in the changes in text boxes for
Field1, Field2, etc. (using 9/30/06 and prior) and just produce a total for
the previous quarter (which would be 6/30/06 and prior).
Let me know if you need more information.

I appreciate your guidance.
Thanks.

OK. I've figured out the zero thing and calculating the rows.... with Val.
Now I just need to subtotal in the Country Footer because that's how the
report is grouped by.

Also, if there is no value in the text box (just nothing, not even a 0), the
row does not calculate correctly.

:
I have a report that pulls information from a Select Query. A form dictates
what information appears in the unbound text boxes (in the details section),
using

Month1.ControlSource = Forms![frm_EnterData]![txtMonth] + 0

I want to be able to calculate sums based on the unbound text boxes... can
this be done? I also want to sum these after each grouping.

Check into using the Nz function instead of Val.

I also think you probably should use the Value property
instead of the ControlSource property. The ControlSource is
supposed to be a field name or an expression that starts
with an = sign/
Month1.Value = Nz(Forms!frm_EnterData!txtMonth, 0)

To sum up the unbound values, add a (hidden?) text box named
txtRunTotal next to the Month1. Set its Control Source to
=Month1 and its RunningSum property to Over Group. Then a
group footer text box can display the group total by using
the expression txtRunTotal. A similar arrangement can be
used to get a grand total in the report footer.

 

[sunshineleo]

Can anyone help?

I tried what you said but every time I execute the On Click, it reverts back
to the old code, but using Value instead of Control Source, the rest of the
code reverts back to the old way? What's it's problem?

Also, I have another report with set fields in the header with the values in
the details field.

Q2-2006 Field1 Field2 Field3 Field3 Q3-2006
Item
Item
Item
Total by group

There are 22 fields... I want to be able to have the user pick the quarter
they need, i.e. Q3-2006, which would fill in the changes in text boxes for
Field1, Field2, etc. (using 9/30/06 and prior) and just produce a total for
the previous quarter (which would be 6/30/06 and prior).
Let me know if you need more information.

I appreciate your guidance.
Thanks.

OK. I've figured out the zero thing and calculating the rows.... with Val.
Now I just need to subtotal in the Country Footer because that's how the
report is grouped by.

:

Also, if there is no value in the text box (just nothing, not even a 0), the
row does not calculate correctly.

:
I have a report that pulls information from a Select Query. A form dictates
what information appears in the unbound text boxes (in the details section),
using

Month1.ControlSource = Forms![frm_EnterData]![txtMonth] + 0

I want to be able to calculate sums based on the unbound text boxes... can
this be done? I also want to sum these after each grouping.

Check into using the Nz function instead of Val.

I also think you probably should use the Value property
instead of the ControlSource property. The ControlSource is
supposed to be a field name or an expression that starts
with an = sign/
Month1.Value = Nz(Forms!frm_EnterData!txtMonth, 0)

To sum up the unbound values, add a (hidden?) text box named
txtRunTotal next to the Month1. Set its Control Source to
=Month1 and its RunningSum property to Over Group. Then a
group footer text box can display the group total by using
the expression txtRunTotal. A similar arrangement can be
used to get a grand total in the report footer.

 

[Marshall Barton]

Are you saying that the VBA editor is reverting to the old
expression?? That is weird beyond imagination and I have
absolutely no idea how that could happen unless the module
has become corrupted. You are not trying to edit code while
the form is open in any view other than design view are you?

I don't understand your other question, so you should start
another thread.
--
Marsh
MVP [MS Access]

I used the NZ Statement in the Query, transform.
I tried to change

Month1.ControlSource = Forms![frm_EnterData]![txtMonth] + 0

to

Month1.Value = Nz(Forms!frm_EnterData!txtMonth, 0)

but it changes to

Month1.Value = Forms![frm_EnterData]![txtMonth] + 0

What's up with that? It's strange.

I have another question.... maybe I should start another thread....?

I have another report with set fields in the header with the amounts in the
details section... I want to be able to have the user pick the start quarter
for the report. How do I do this? It only shows the first quarter in the
report. Can something be done with the OnFormat? I need them to be able to
pick the start quarter and have the report show all the values for those
fields in that quarter, plus I need it to show a total for the previous
quarter. Do you need any more information?

I have the totals by quarter in a Select Query.

Thanks.

OK. I've figured out the zero thing and calculating the rows.... with Val.
Now I just need to subtotal in the Country Footer because that's how the
report is grouped by.

:

Also, if there is no value in the text box (just nothing, not even a 0), the
row does not calculate correctly.

:
I have a report that pulls information from a Select Query. A form dictates
what information appears in the unbound text boxes (in the details section),
using

Month1.ControlSource = Forms![frm_EnterData]![txtMonth] + 0

I want to be able to calculate sums based on the unbound text boxes... can
this be done? I also want to sum these after each grouping.

Check into using the Nz function instead of Val.

I also think you probably should use the Value property
instead of the ControlSource property. The ControlSource is
supposed to be a field name or an expression that starts
with an = sign/
Month1.Value = Nz(Forms!frm_EnterData!txtMonth, 0)

To sum up the unbound values, add a (hidden?) text box named
txtRunTotal next to the Month1. Set its Control Source to
=Month1 and its RunningSum property to Over Group. Then a
group footer text box can display the group total by using
the expression txtRunTotal. A similar arrangement can be
used to get a grand total in the report footer.