DSUM expression #error question

[AlfredNor]

Two questions:

First I need to put the sum for a range of values in a subform in the master.

Im thinking I use the DSum(expr, domain[, criteria]) model.

But I get an #error when I use:
=DSum([Forms]![ReferralTable subform]![Referral Amt],[ReferralTable subform])

Am I getting the error because Im picking the domain value incorrectly or because I ommitted the critera?)

Second I need to duplicate this but pick only the values where [DateField A] is older than [DateField B]

So do I use =DSum([Forms]![ReferralTable subform]![Referral Amt],[ReferralTable subform][,[DateField A]<[DateField B]) ??

 

[DBL]

For the first bit you need to do it in two stages. In the footer of the subform create an unbound text field and in the control source do:

=Sum ([nameoffieldtosum])

Then create an unbound text field on your main form and reference it by:

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

the NZ function will return a value of 0 if the subform is emptry, rather than saying Error#

 

[AlfredNor]

Ok...

Ok GREAT...got it to work

Now...how do I do part 2?
Each Amt has a date that it's attatched to...I need it to be summed if and only if the date it has attatched to it is earlier than today Now()

 

[Rich]

Use Date() rather than now as default value unless you want the time element included as well.
=Sum(Iif([MyDateField]<Date(),[Credits],0))

 

[AlfredNor]

thanks

hmm... I just put that in and am getting an #error.
Let me check the references (since I got the first sum to work) and see whats up.

 

[AlfredNor]

Arrgh!

The program is corrupting ITSELF!

When I go back into the forms, the []'s are coming off...and it continually adds the [] around [Form]!

Why is this happening? I had the first sum working...now both are in #error.

This is the last part of this form...and Ill be done...sigh. Help!

P.S.
I just checked..and it even corrupted the form I had saved under a different name - ( I guess bec its from the same tables) that was working!