DSUM

[buckeye77]

Is there a way to have "DSUM" add all the numbers of a particular feild of a subform for the current record only?

I.E.
Person A in main form
2008 = 2000 in subform
Person B in main form
2008 = 4000 in subform

If I have a DSUM in an unbound in the main form = DSUM([2008]), regardless if Person A or Person B's record is showing, the amount is equal to 6000.

Any suggestions?

 

[Remou]

Put sum in the subform footer:

=Sum([2008])

Then reference the control in the main form:

=[SubformControlNameHere].Form.[SumControlNameHere]

 

[buckeye77]

Problem...
In the subform, the feild is [curAdvance] and in the detail portion of the form there is also a field [calandaryear].
[2008] = =IIf([CalandarYear]=2008,[curAdvanceAmt],0)
How would I sum [2008] for the form? It keeps showing #ERROR when I try.

 

[Remou]

Have you tried:

=Sum(IIf([CalandarYear]=2008,[curAdvanceAmt],0))

 

[buckeye77]

Thank you sooo much! Getting Goose bumps.... slowly working... Now to see if I can use this to work with the other subform....


Again, thanks!

 

[buckeye77]

Found an issue.... I added another subform that has the same formula, only this subform is "reimbursements". Now the main form 2008 =[frmAdvance].Form!Text23+[frmReimbursements].Form!Text23

The problem is that even with a "refresh" on the on exit of the subform of advances, the amount disappears in 2008. no #error or name?, the field simply goes blank.

 

[buckeye77]

Sorry to keep posting but thought this may help someone help me.... The amount will show in the main form, only if both subforms have amounts.... but if only one or the other, the main form does not add.

 

[Remou]

Try Nz:

=Nz([frmAdvance].Form!Text23,0)+Nz([frmReimbursements].Form!Text23,0)

 

[buckeye77]

Works! But how? Something to do with null values?

Regardless... Thanks again!

 

[buckeye77]

Slightly new question.... if the subforms are displayed in datasheet view, is there a way to change the column headings? Already tried changing the caption in the tables and already tried changing the "name" in the design portion of the form.

 

[Remou]

It is best not to use tables in forms. You can set up a query with whatever column names you want, but it is best to use a fairly standard query and a continuous form, rather than a datasheet.

It is possible to add captions to a set of controls on the detail part of a form and then view it as a datasheet - this should also allow you to change column names, but it is not very satisfactory.

 

[buckeye77]

Thanks for the thought and warning that adding captions may not be very satisfactory, however at this time, I think it is my best option. Can you assist?

 

[Remou]

Open a new form. Set the recordsource to the table or query for the datasheet. Drag the fields from the field list (toolbar) onto the form. They will arrive with labels. Edit the label captions. Set the form's default view to datasheet.