Sum from a subform

[winkmich]

Hi there,

Just a (maybe) simple question.

I have a form A with a subform B.
Subform B contains a list of donations (datasheet with ID, Date, Amount) which were given by the Person shown in Form A.

I’d like to include a field showing the sum of the donations.

When I enter a field into Form A with:
=Sum([Forms]![Subform B]![Amount])

it always shows me the expression: #Name?

So, I just assume it cannot find the right expressions/parameters.

Can anyone help?

Thanks in advance.

 

[DBL]

First of all, create an unbound field in the footer of the subform and do the calculation there

=Sum([amount])

Then you can reference that field on the other form:

=Nz([subformname].Form![fieldname],0)

the Nz function returns a value of 0 if the subform is empty, otherwise you'll get an Error#

 

[winkmich]

Thanks for the hint:

Actually it should be working like this - but, doesn't

I created a field in the subform called "subtot" and the sum is calculated correctly.

but when included into the Main form like this

=Nz([subformname].[Form]![Subtot],0)

it still returns: #Name?

Any Idea?

Mike.

 

[DBL]

Without wanting to sound to stupid, can I just check that where you've put [subformname] you have actually put the name of your subform?

The best way to do it would be to use the build button beside the recordsource for the text field on the main form to reference back to the subform field.

 

[winkmich]

Thanks for the hints.

It was correct that the name of the field was different - I changed that into "Subtot".
The name of the subform was correct

Anyway, Still it did not work.

I tried the expression builder, tried a new form, tried a new subform - no way - always the same.

Can it be a simple mistake which I made?

Maybe it's better to wait 2 days and try again

 

[rheide]

Does anyone have any suggestions for this problem. I am experienceing the same problems that winkmich is. I know the code is referencing the subform correctly because when there are records, it returns a number rather than #Error. I just can't figure out why it won't work. I'm using Access2002 if it matters.

Thanks, Randy

 

[DBL]

It's happening when there are no records on the subform? Try the IsNumeric function in the control source for the field that shows the calculation (thanks Rich, invaluable):

=IIf(IsNumeric([subformname].Form![fieldname]),[subformname].Form![fieldname],0)

It returns a result of 0 if the subform is null

 

[rheide]

Unbelievable, it works great. Your a life saver. Thanks to you and it looks like a thanks goes out to Rich.

Have a great day,
Randy