Help with Null and nz

  • Thread starter Thread starter Guest
  • Start date Start date
G

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]))
 
Try this:

=Nz(DSum("[PaymentAmount]","[Payments]","[OrderID]=" &
[Reports]![Statement]![OrderID]),0)
 
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

Ken Snell said:
Try this:

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

--

Ken Snell
<MS ACCESS MVP>


Bmini said:
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]))
Click to expand...
Click to expand...
 
You're welcome.

--

Ken Snell
<MS ACCESS MVP>

minimom said:
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

Ken Snell said:
Try this:

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

--

Ken Snell
<MS ACCESS MVP>


Bmini said:
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
Click to expand...
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
Click to expand...
amount.
And
then give a Total Balance Due (sum of all balance due on report) at
Click to expand...
the
end
of the report. I have tried =Sum on the report footer, with no
Click to expand...
results.
Is
it because of the Null value problem I am having?
Click to expand...
=IIf(IsNull([OrderID]),0,DSum("[PaymentAmount]","[Payments]","[OrderID]="
&
[Reports]![Statement]![OrderID]))
Click to expand...
Click to expand...
Click to expand...
 
Back
Top