Totaling??? Can you really do it in MSAccess?

[JPM]

Hi,

Is it a fact that you cannot do any totaling off the textbox values in a
report? e.g. In footer of a group, textbox control source would
=SUM([text1]), with text1 being a textbox in a subgroup.

I understand that you can use the SUM() function on the field of the
recordsource and by putting it in a group, have it total the values in the
field that are used within the group. That isn't good enough. I need to do
some math [using an IIF() or VBA function call) based the totals of a field
value within a group. I then need to use the result of that math as part of
another subtotal and a grandtotal.

Tell me it can be done. I've have someone assert that it may be done. I
don't see that happening. Can you prove me wrong?


Thanks,
JPM

 

[Duane Hookom]

You could Sum() your function call and IIf() expression like:
=Sum(GetValue([FieldA], [FieldB]) * IIf([FieldC]=5,34,55) )
Otherwise, you may need to use a running sum in the detail section on a text
box named txtRS and then add a text box to the footer with a control source
of =txtRS.

 

[JPM]

Thanks. It appears that using a running sum on the hidden textboxes is the
solution. Much simpler than what I was envisioning having to do.

You could Sum() your function call and IIf() expression like:
=Sum(GetValue([FieldA], [FieldB]) * IIf([FieldC]=5,34,55) )
Otherwise, you may need to use a running sum in the detail section on a text
box named txtRS and then add a text box to the footer with a control source
of =txtRS.

--
Duane Hookom
MS Access MVP

Hi,

Is it a fact that you cannot do any totaling off the textbox values in a
report? e.g. In footer of a group, textbox control source would
=SUM([text1]), with text1 being a textbox in a subgroup.

I understand that you can use the SUM() function on the field of the
recordsource and by putting it in a group, have it total the values in the
field that are used within the group. That isn't good enough. I need to do
some math [using an IIF() or VBA function call) based the totals of a field
value within a group. I then need to use the result of that math as

part

of
another subtotal and a grandtotal.

Tell me it can be done. I've have someone assert that it may be done. I
don't see that happening. Can you prove me wrong?


Thanks,
JPM

 

[Alex Jones]

I have found a way to Total but depending on what you are
using a MDB or ADP I use a Access ADP when programming.
1.If you are wanting to total in the report only add a
unbound text box to the report and name it Total the
control property can be [subtotal] + [subtotal1] and set
the format property to general number or money which is
perfered.and set it to sum on group

2. The second way if like me I made a stored procedure
with a grid Query and did my math there by haveing the sp_
Grid query sum the numbers that I am placing in the group
by using the query with parameters. you can also add
functions to the grid like tables and have the math done
in each funtion query so you are only placing the field
being return to the sp_grid procedure. and then referance
the unbound text box to it in the control source property.

If this all sound confussing it really is not just takes
some logic. if an error pops up when saving the sp_grid
then you will manually have to add the parameters to the
funtion call in the SQL code. This has saved me alot of
time because I can change the funtion only without going
in to the VB code of the report or screen. I hope this
helps if I understood the question right.

-----Original Message-----
You could Sum() your function call and IIf() expression like:
=Sum(GetValue([FieldA], [FieldB]) * IIf([FieldC] =5,34,55) )
Otherwise, you may need to use a running sum in the detail section on a text
box named txtRS and then add a text box to the footer with a control source
of =txtRS.

--
Duane Hookom
MS Access MVP

Hi,

Is it a fact that you cannot do any totaling off the textbox values in a
report? e.g. In footer of a group, textbox control source would
=SUM([text1]), with text1 being a textbox in a subgroup.

I understand that you can use the SUM() function on the field of the
recordsource and by putting it in a group, have it total the values in the
field that are used within the group. That isn't good

enough. I need to
do

some math [using an IIF() or VBA function call) based

the totals of a
field

value within a group. I then need to use the result of

that math as part
of

another subtotal and a grandtotal.

Tell me it can be done. I've have someone assert

that it may be done.
I

don't see that happening. Can you prove me wrong?


Thanks,
JPM

.