DSUM Formula Not Working

0
votes

I have a DSUM formula in my Database query that uses below formula to work out the remaining outstanding amount to be paid on an invoice.

It works fine in most cases, however it doesn't work where there is an Invoice that has not yet had any Payments, in that case there are no payments to refer to and the formula produces an '#Error' in the formula cell.

How could I change the below to get it to work for Invoices that have no Payments please?

Many Thanks

Amount Outstanding Before Payment: IIf([Invoice Amount]=0,0,IIf([Invoice Amount]-Nz(DSum("[Amount Paid]","Payments Table Combined with Invoice Table","[Invoice Number] = " & [Invoice Number] & " And [Payment Date] < " & Format([Payment Date],"#yyyy-mm-dd#")),0)=0,0,[Invoice Amount]-Nz(DSum("[Amount Paid]","Payments Table Combined with Invoice Table","[Invoice Number] = " & [Invoice Number] & " And [Payment Date] < " & Format([Payment Date],"#yyyy-mm-dd#")),0)))

1
ms-accessms-access-2013

1 Answers

0
votes

You can reduce this and use Nz for an empty Payment Date:

Amount Outstanding Before Payment: IIf([Invoice Amount]=0,0,[Invoice Amount]-Nz(DSum("[Amount Paid]","Payments Table Combined with Invoice Table","[Invoice Number] = " & [Invoice Number] & " And Nz([Payment Date],Date()) < " & Format(Nz([Payment Date],Date()),"\#yyyy-mm-dd\#") & ""),0))