#Error shows up when total is zero even with Nz function

Thank you both Klatuu and Marshal
Before getting the Marshal's post I tried the "onNoData" property of the
report and that solved the problem by giving a message to the user and
returning to the range selection form.


"Klatuu" wrote:

Thanks, Marsh. I didn't think of that.
As my code never has errors, it is easy to forget about that function
(would you like to buy some land in Florida?)
--
Dave Hargis, Microsoft Access MVP


"Marshall Barton" wrote:

I can't see all of the posts in this thread, but if you want
to display a specific value instead of #Error, then check
for an error:

=IIf(IsError(Sum(x)), y, Sum(x))
--
Marsh
MVP [MS Access]


Moe wrote:

Yes. And I wrote to you about the result. It works as long as at least one
item is returned by the query. If, from the query, the [sumOfSubtotals] is
zero (meaning query returns one row with the value 0)then the
sum(Nz([sumofsubtotals], 0)) on the report is correctly zero. But if query
does not return any thing (like no record exists with in the date range) then
the total on the report, sum(Nz([sumofsubtotals], 0)), is #Error. When query
returns at least one non-zero or zero record then sum(Nz([sumofsubtotals],
0)) works correctly. so, the problem is when query does not find any thing
and as a result on the report "sum" is dealing with non-existing rows and
nothing else, then NZ function does not work.

"Klatuu" wrote:

Have you tried this yet:

Since the above is not working for you, I suspect the sumOfSubtotals field
is actually a sum created in a query. Go to that query and use the Nz
function on the value before it is summed.

--
Dave Hargis, Microsoft Access MVP


"Moe" wrote:

How can I do that? Also, I see some other people have similar problems. Is
this an over looked problem in Access or "Sum" and "Nz" functions are to be
used differently when applied to numbers which are themselves created by
"sum" function in a query? I am trying to use this in a report, not a form.
Moe

"Klatuu" wrote:

Not sure what this is, but the first thing I would try is only performing the
calculation if there is at least one item.
--
Dave Hargis, Microsoft Access MVP


"Moe" wrote:

Thanks Klatuu
I tried that and now my Query which generates the "sumofsubtotals" returns
$0.00 when an items value is 0.00 (it used to be blank) the
Sum(Nz([sumOfSubtotals],0)) also return 0.00 when the total is 0.00 as long
as there is at least one item in the date range (with zero as its value).
when no item exists in the date range the Sum(Nz([sumOfSubtotals],0)) retuns
#Error This is an example:
item date sumofsubtotals
IT1 someDate $0.00
Total $0.00
But if no item exists the total shows Error

"Klatuu" wrote:

The Nz function needs to be wrapped around the lowest level number. You
second example should work, but has some extraneous verbage.
Instead of:
=Nz(Sum(Nz([sumOfSubtotals],0)),0)
Try:
=Sum(Nz([sumOfSubtotals],0))

Since the above is not working for you, I suspect the sumOfSubtotals field
is actually a sum created in a query. Go to that query and use the Nz
function on the value before it is summed.
--
Dave Hargis, Microsoft Access MVP


"Moe" wrote:

what is wrong with this expression
"=Nz(Sum([sumOfSubtotals]),0)"
I also tried" =Nz(Sum(Nz([sumOfSubtotals],0)),0)"
How can we use "Sum" and Nz together?
It works when the sum returns a non-zero.