#Error summarizing calculated fields on a form

MS Access 2013:  I have a subform with some calculated fields in the detail.  I want to summarize these fields in the footer of the report but the summary fields in the footer of the report show '#error'.

This is one of the calculated fields in the Form 'Detail' section, Name:'AccumPayAmt', Control Source: '=IIf([PostedStatusID]=3,0,[PayAmt])'

The calculated fields are showing the correct amount.

This is one of the summary fields in the form 'Footer' section: Name 'Text108', Control Source: '=Sum([AccumPayAmt])'

There are a few other fields but all are the same construct.


Can anyone spot the problem?
LVL 1
mlcktmguyAsked:
Who is Participating?
 
Gustav BrockCIOCommented:
Try summing the source:

=Sum(IIf([PostedStatusID]=3,0,[PayAmt]))

Open in new window

0
 
PatHartmanCommented:
No points please since Gus gave you the answer.  Access maintains only a single set of properties for a form so even though you see multiple instances of the subform, there is still only one set of properties.  That is why you have to sum the underlying data and perform the calculations again.
0
 
mlcktmguyAuthor Commented:
Thank you Gustav for the answer and Pat for the explanation.

Pat: At your request I awarded all of the points to Gustav
0
 
PatHartmanCommented:
Thanks.  He gave you the answer but I thought you should also know why what you were doing would not work since it wasn't a mystery.  Your code was what 100% of newcomers would have tried and usually a good guess like that should be rewarded but not this time.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.