Can't get sum in subform footer to work.

[wordbrew]

Hello all,

I searched the forums as much as I can and haven't found a viable solution.

I have a subform that filters the records by Publisher.

In the subform I have a calculated control called "% of Track Commission", that takes the subform control "# of Writers" and divides it by the subform control "# of Writers for Track".

From this I have another control called "TotalTrackCommission" that then takes "% of Track commission" and multiplies it with "Commission Per Track."

All of this works fine.

The problem arises when I try to put a text box control in the subform footer to sum the "TotalTrackCommission" text box control for the current records shown.

Currently I'm trying to use =Sum([TotalTrackCommission]), but this only returns #Error. If I use =([TotalTrackCommission]) it does return a value, but obviously only for one of the values, not the sum of them. I know my problem probably lies in the fact that I'm trying to sum a calculated control (which is calculated off another calculated control LOL), but I just don't know the avenues I should be trying to take.

So I would appreciate any insight, whether I'm just referencing it wrong, or if I should somehow utilize a query, etc... My ultimate goal of course is to have a text box control on the main form pull the sum from this footer control.

Thanks for you time!

As a side note, is there a way to have another text box control "CommissionPerTrack" in the subform, take it's value from a control in the main form, or from a table unrelated to the one used by the subform? I tried adding the other table to the subforms record source so I could utilize it, but even though that works, it locks the controls for publisher where I can't change anything in the control boxes anymore. Right now I just set the control to a value of .091, but I would rather it pull this from the main form (or table) as this value is subject to change, and I won't be the one ultimately using the database, so this value needs to be changed easily. The form I'm trying to pull from is frmAlbumCommission, and the control is "Commission Per Track", or the table possibility is tblAlbumCommissions, and the field is "CommissionPerTrack". I know the names could be differentiated better, but it is what it is you know.

Again I appreciate any help.

[nico5038]

You can't use "calculated controls" to sum, use instead the sum of the calculation like:

=sum([# of Writers"] / [# of Writers for Track])
 

I would also recommend to use no special characters and/or spaces in your field names.
Use e.g. [NumOfWriters], [NumOfWritersForTrack], etc.

Nic;o)

[wordbrew]

Hey Nico, I really appreciate your response to my problem. And I agree with you that I should lose the special characters and spaces. I'm proficient in access and i'm slowly losing the bad habits lol.

I had actually tried using that solution before I posted but for some reason I still couldn't get it to work (lol could be user error?).

But that did ultimately solve my problem. I really appreciate it. I then ended up using another calculated control in the footer that multiplied this result by another text box in the main form to get another sum result that I couldn't reference directly (another calculated field in the subform), but was able to work around that problem using this route.

Here's hoping there's always a work around to any problem we encounter.

Thanks again!

[nico5038]

I sometimes add the calculation to the query and use it in the footer's SUM().

Nic;o)