SUM of fields in a subform

  • Thread starter Thread starter Sharon
  • Start date Start date
S

Sharon

Hello,

I have a subform in which I pull data from another table
using a combo box. I then fill in a 2nd column in the
subform by using the select query from my combo box like
this.
=[Cbo40-1 Number].Column(2) -- this views the value of the
amount field in the 40-1 table.

My problem is that I now would like to sum up the values
of that =[Cbo40-1 Number].Column(2) field in a list.
However, it does not appear that SUM works. Can someone
please assist me getting past this hurdle?

Thanks so much!
Sharon
 
You might try this. I think this has happened to me
before. If this does not work you may need to repost the
question.

It sure seems like the Sum([field]) would work but if not
try putting the Sum([Field]) in the detail section and
then reference the name of that form object in the the
form footer for your total. Then when it is working
correctly "Send to Back" the object in the detail section
so it will not be seen. I also redirect the cursor when
the object in the detail section gets focus because I do
not actually want the user in that field. I do this
because I think when I tried to set visible to NO for my
object that has the SUM([Field]) in the detail section I
would get an incorrect total. Hope this helps.
 
Sharon,

The Sum() function should work for this purpose. Do you have it in
the controlsource of a textbox in the Form Footer section of the
subform? What is the expression you are using?

By the way, as a side issue, it is not a good idea to use a - as part
of the name of a field or control or database object.

- Steve Schapel, Microsoft Access MVP
 
Steve -

I have it in the Form Footer of the subform & the
expression I have in the Control Source box is:

=Sum([Cbo40-1 Number].Column(2))

But it just says "Error" in the textbox on the form.

Thanks for the tip about the - in the field name. Does
that mean I should change it in the original table & every
query I have it in? YIKES... I should have read the rules
books before I got into the mess :-)

Thanks for your help!

Sharon
-----Original Message-----
Sharon,

The Sum() function should work for this purpose. Do you have it in
the controlsource of a textbox in the Form Footer section of the
subform? What is the expression you are using?

By the way, as a side issue, it is not a good idea to use a - as part
of the name of a field or control or database object.

- Steve Schapel, Microsoft Access MVP


Hello,

I have a subform in which I pull data from another table
using a combo box. I then fill in a 2nd column in the
subform by using the select query from my combo box like
this.
=[Cbo40-1 Number].Column(2) -- this views the value of the
amount field in the 40-1 table.

My problem is that I now would like to sum up the values
of that =[Cbo40-1 Number].Column(2) field in a list.
However, it does not appear that SUM works. Can someone
please assist me getting past this hurdle?

Thanks so much!
Sharon
Click to expand...

.
Click to expand...
 
Sharon,

Humble pie time! I must apologise, I wasn't thinking clearly before.
No, this sum will not work in the form footer. The Sum function must
apply to fields which are in the recordsource of the form... which
[Cbo40-1 Number].Column(2) is not. Sorry.

So, here's an idea... make it part of the recordsource of the form.
This will be simple... just base your form on a query which includes
the table which you are using for the rowsource of the combobox,
joined to the main table by whatever field [Cbo40-1 Number] is bound
to. Then, you can simply include this Amount field directly on the
form, scrap the [Cbo40-1 Number].Column(2) control, and your footer
control should work with =Sum([Amount])

For further information on the "other matter", see the bottom of
http://support.microsoft.com/default.aspx?scid=kb;EN-US;209187

- Steve Schapel, Microsoft Access MVP
 
Back
Top