Help with Null and nz

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bmini - 23 Oct 2004 22:19 GMT

I have a report called "Statement", it is a billing statement

The expression below works to calculate payments that have been made to a
particular orderID. However I need it to insert $0.00 if there has been no
payments made for the order. I have tried using nz to no avail. How would I
write the expression below to also handle a null in the payment amount. And
then give a Total Balance Due (sum of all balance due on report) at the end
of the report. I have tried =Sum on the report footer, with no results. Is
it because of the Null value problem I am having?

=IIf(IsNull([OrderID]),0,DSum("[PaymentAmount]","[Payments]","[OrderID]=" &
[Reports]![Statement]![OrderID]))

Reply to this Message

Ken Snell [MVP] - 24 Oct 2004 00:03 GMT

Try this:

=Nz(DSum("[PaymentAmount]","[Payments]","[OrderID]=" &
[Reports]![Statement]![OrderID]),0)

Signature

Ken Snell
<MS ACCESS MVP>

> I have a report called "Statement", it is a billing statement
>
[quoted text clipped - 8 lines]
> =IIf(IsNull([OrderID]),0,DSum("[PaymentAmount]","[Payments]","[OrderID]=" &
> [Reports]![Statement]![OrderID]))

Reply to this Message

minimom - 25 Oct 2004 15:55 GMT

THANKS so very much, I can't believe I how simple you made this, I racked my
brain with every other expression except this one. Thanks again

> Try this:
>
[quoted text clipped - 19 lines]
> &
> > [Reports]![Statement]![OrderID]))

Reply to this Message

Ken Snell [MVP] - 25 Oct 2004 20:38 GMT

You're welcome.

Signature

Ken Snell
<MS ACCESS MVP>

> THANKS so very much, I can't believe I how simple you made this, I racked my
> brain with every other expression except this one. Thanks again
[quoted text clipped - 18 lines]
> > Is
> > > it because of the Null value problem I am having?

=IIf(IsNull([OrderID]),0,DSum("[PaymentAmount]","[Payments]","[OrderID]="

> > &
> > > [Reports]![Statement]![OrderID]))

Reply to this Message