SUM in subform

  • Thread starter Thread starter Geoff
  • Start date Start date
G

Geoff

I'm using the SUM command in an unbound text box, in the footer of a
subform, to get the total of numbers in a field on that form (which is a
continuous one by the way). That works fine, but the SUM command is
referencing a field in the query that the subform is based on.

I now want to put another SUM command to total up another column, but I want
to reference the name of the control it's totalling up rather than
referencing the query. I have to do this because the formula for the control
source of this control gets some of it's data from the main form. This just
gives me #error though. In fact when I tried using the name of the control
rather than the query's field name in the other text box, that stopped
working.

Surely it must be possible to use the SUM command by referencing the control
name, and it's just a case of understanding how to reference the control? I
hope so anyway. IF it isn't possible though, I would need to put a formula
in the query that references a control on the main form. How would I do that
please?

Thanks for any help,
Geoff.
 
If I am understaning your post correctly, you need to put the source for the
control you are referencing in your sum.
example: Field1 = forms!Mainfrm!Total + me.currentcharges
SumField = sum(forms!Mainfrm!Total + me.currentcharges)
HTH
Kat
 
Katrina said:
If I am understaning your post correctly, you need to put the source for the
control you are referencing in your sum.
example: Field1 = forms!Mainfrm!Total + me.currentcharges
SumField = sum(forms!Mainfrm!Total + me.currentcharges)
HTH
Kat
Click to expand...


Hi Kat. Thanks for your reply but I'm not sure I really understand where to
put what. If you don't mind I'll try and explain it better than I did
before, leaving out unnecessary details.

I have a subform on a form. The subform is set to display continuous
records. The name of one of the fields is 'carried' and it has a long
formula in the control source box that refers to the main form.

In the footer of the subform, I want to get the total of the numbers
displayed in all the 'carried' boxes that are currently displayed in the
subform. So I've created a text box in the footer, and I tried putting this
in it's control source:

=Sum([carried])

That gives me an error though.

So would you be so kind as to tell me what I could write as the control
source for that text box in the subform's footer please? I have a feeling I
can't do this without a query, and I've tried doing it that way but failed.

Thanks loads,
Geoff.
 
you have to put the value of the controlsource for carried in the sum() for
the footer
so, copy the controlsource from the carried field
in the controlsource for your total field in the footer type = sum(
then paste the controlsource from the carried field
then put your ending )

if you still don't understand, If you will include the controlsource for
Carried in your next reply, I will give you the exact code to put in the
controlsource for your total field.

Kat

Geoff said:
Katrina said:
If I am understaning your post correctly, you need to put the source for the
control you are referencing in your sum.
example: Field1 = forms!Mainfrm!Total + me.currentcharges
SumField = sum(forms!Mainfrm!Total + me.currentcharges)
HTH
Kat
Click to expand...


Hi Kat. Thanks for your reply but I'm not sure I really understand where to
put what. If you don't mind I'll try and explain it better than I did
before, leaving out unnecessary details.

I have a subform on a form. The subform is set to display continuous
records. The name of one of the fields is 'carried' and it has a long
formula in the control source box that refers to the main form.

In the footer of the subform, I want to get the total of the numbers
displayed in all the 'carried' boxes that are currently displayed in the
subform. So I've created a text box in the footer, and I tried putting this
in it's control source:

=Sum([carried])

That gives me an error though.

So would you be so kind as to tell me what I could write as the control
source for that text box in the subform's footer please? I have a feeling I
can't do this without a query, and I've tried doing it that way but failed.

Thanks loads,
Geoff.
Click to expand...
 
Try this?

In the control on the subform where you want to show the total:
=nz(Sum([fieldname]))

I 'borrowed' this from one of the Access template databases. I have n
idea what the 'nz' means or is for, but it does the trick for mine.

Hope this helps you..
 
Katrina said:
you have to put the value of the controlsource for carried in the sum() for
the footer
so, copy the controlsource from the carried field
in the controlsource for your total field in the footer type = sum(
then paste the controlsource from the carried field
then put your ending )

if you still don't understand, If you will include the controlsource for
Carried in your next reply, I will give you the exact code to put in the
controlsource for your total field.

Kat
Click to expand...


Thanks for that Kat. A little earlier today I read about that so tried it
but it didn't work. I don't know why not, but I also read that several other
people had the same problem. I have since re-structured my database and done
all my calculations in a query instead. I couldn't do that before because
one of the calculations referred to the main form, but I've worked out how
to get around that now and I'm generally happier with how the database is
all put together. So now I can do the SUM and just refer to the control
name, and it works fine. :)

Thanks very much for taking to time to help me out Kat. :)

Geoff.
 
YW

Kat
Geoff said:
Thanks for that Kat. A little earlier today I read about that so tried it
but it didn't work. I don't know why not, but I also read that several other
people had the same problem. I have since re-structured my database and done
all my calculations in a query instead. I couldn't do that before because
one of the calculations referred to the main form, but I've worked out how
to get around that now and I'm generally happier with how the database is
all put together. So now I can do the SUM and just refer to the control
name, and it works fine. :)

Thanks very much for taking to time to help me out Kat. :)

Geoff.
Click to expand...
 
Polly said:
Try this?

In the control on the subform where you want to show the total:
=nz(Sum([fieldname]))

I 'borrowed' this from one of the Access template databases. I have no
idea what the 'nz' means or is for, but it does the trick for mine.

Hope this helps you...
Click to expand...


Hi Polly. I read that Nz is like IIF but not quite the same! :) I haven't
investigated the difference yet and won't bother until I need to use it. I'm
not sure how the way you quoted it would work in my situation but it doesn't
matter now anyway because I've resolved it now. I've re-structured my
database so that several calculations that were done on the subform are now
done in a query, and it makes a lot more sense now and because I'm only
totally up a control and not a formula now, the SUM calculation works as it
should.

Thanks anyway Polly. :)

Geoff.
 
Back
Top