Sum field values in form's footer returns #Error

[TimTDP]

On a form I have the following field:

Name: PaymentsMade
Control Source: =DLookUp("SumOfPrePaymentValue","qryInvoicePrepaymentSum","InvoiceId = " & [InvoiceId])

In the form's footer I have the unbound text control:
Name: SumOfPaymentsMade
Control Source: =Sum([PaymentsMade])

SumOfPaymentsMade returns #Error

Why?
How do I get it to return the sum of PaymentsMade?

Thanks

 

[pr2-eugin]

Are you sure the PaymentsMade is not returning any Null? Why not JOIN the Query to the RecordSource of the Form and that way it will not need DLookUp?

Did you try using DSum instead of Sum?

 

[TimTDP]

re you sure the PaymentsMade is not returning any Null?

I have changed the Control Source of PaymentsMade to: =Nz,DLookUp("SumOfPrePaymentValue","qryInvoicePrepaym entSum","InvoiceId = " & [InvoiceId]),0)
This will ensure that PaymentsMade is never null
Still get #Error

Why not JOIN the Query to the RecordSource of the Form

Because "qryInvoicePrepaymentSum" is a totals query. If I join it, I cannot edit records on hte form

Thanks

 

[pr2-eugin]

So did you look at DSum?

I am sure that is a typo..

=Nz,DLookUp("SumOfPrePaymentValue","qryInvoicePrepaym entSum","InvoiceId = " & [InvoiceId]),0)

It should be..

=Nz[COLOR=Red][B]([/B][/COLOR]DLookUp("SumOfPrePaymentValue","qryInvoicePrepaym  entSum","InvoiceId = " & [InvoiceId]), 0)

 

[TimTDP]

A typo in my post. Sorry about that.
It is correct on the form