Help with Null and nz

[Guest]

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]))

 

[Ken Snell [MVP]]

Try this:

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

 

[Guest]

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:

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

--

Ken Snell
<MS ACCESS MVP>

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]))

 

[Ken Snell [MVP]]

You're welcome.

--

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

Try this:

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

--

Ken Snell
<MS ACCESS MVP>

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]))