Sum one field with itself from multiple records?

[Jon Henry]

I have a payments table witha field(lots) that I want to sum. The payments table is related to the developments table so I can have quite a few payment records tied to one development.
I have a form based off of a developments query with a subform based of a payments query. When the subform is displaying more than one record, I want to be able to sum all the 'lot' fields and display the value in a textbox on the regular form.

How would I go about summing a repeated field with itself?

 

[boblarson]

Put a text box on your subform in the footer and use = Sum(Nz([YourFieldName],0))

Then, in your textbox on the main form use

=[YourSubformControlName]![Form]![YourTextBoxNameInSubFormFooter]

Substitute the subform control name (the container control on the main form that houses the subform) for YourSubformControlName and YourTextBoxNameInSubFormFooter with the name of the text box with the sum in the subform footer. Leave the [Form] part exactly as is.

 

[ajetrumpet]

Sounds like the subform is being filtered by another one of your actions from the main form. If that is the case, maybe you could simply use a DSUM() in the control source of a main form textbox, and update it on a conditional basis whenever the subform changes and has more than one record in it. Possibly something like this:

On a "change" event of some sort

  if me.subformContainerName.form.recordcount > 1 then
    me.TextboxName = DCOUNT("LOTfield", "relevantTable", "SameFilterExpressionThatIsFilteringYourSubform")
      me.requery
  end if

 

[ajetrumpet]

oh Bob, how I hate the NZ function Your solution is better though.

This small application that I just built uses so many invisible controls on subforms that it almost makes me sick. It's a good trick for sure!

 

[Jon Henry]

Thanks for the quick replys.

Bob, I used your way but still have a problem.

I put a tb on the subform footer and set it up as you prescribed. It works, it sums up the tb's for me.

The tb on the main however doesnt work, it only shows #name?

=[Payments Query subform]![Form]![txtSubCtl]

This is correct, right? My subform name is Payments Query subform and the tb on the subform footer is named txtSubCtl.

 

[boblarson]

My subform name is Payments Query subform .

Not the subform name, the name of the control on the main form that HOUSES the subform. It is highly likely that it is not named Payments Query subform and in fact could be named something like Child47 (etc.).

 

[Jon Henry]

That is the name. When I originally put the subform in, it was called 'child'?(dont remember the number) but after going through the subform wizard, it asked for a name at the end giving a default choice which I used. Payments Query subform

 

[boblarson]

See if this sample I just uploaded will help:

http://downloads.btabdevelopment.com/Samples/subforms/SAMPLE-SubformTotalOnMainForm.zip

 

[Jon Henry]

Works like a champ

Problem was first question mark should have been a period

=[Payments Query subform].[Form]![txtSubCtl]

 

[boblarson]

Good, glad you got it. Sorry for putting in the extra exclamation point. I had done that from memory and so apparently my memory needs assistance

 

[Jon Henry]

Np, I appreciate the help and responsiveness. Much easier to try and be a little off than to sit here and twiddle my fingers with no help.