How can I use SUM and Nz together

H

Huntergatherer

I am trying to place a SUM and Nz expresion together in a report. The outcome
should be:
Sum the results - works OK
If no results are returned from the Query to the report then show 0 (zero)

I have tried various combinations of =Nz(Sum([data1])) etc but only get
#error when no data is returned. With data returned works fine.

Probably somthing obvious, but I am a nubie!

Many thanks in anticipation

Peter
 
D

Dirk Goldgar

Huntergatherer said:
I am trying to place a SUM and Nz expresion together in a report. The
outcome should be:
Sum the results - works OK
If no results are returned from the Query to the report then show 0
(zero)

I have tried various combinations of =Nz(Sum([data1])) etc but only
get #error when no data is returned. With data returned works fine.

Probably somthing obvious, but I am a nubie!

Many thanks in anticipation

Peter
Click to expand...

When no records are returned by the recordsource query, there's nothing
to Sum, so what you get is an error, not a Null, and Nz() can't fix
that. Try this:

=IIf([HasData],Sum([data1]),0)

HasData is a property of the report that is False if the report's
recordset is empty, True if not.
 
H

Huntergatherer

Thanks Dirk - solution worked, and the explanation helps understand.

Regards
Peter


Dirk Goldgar said:
Huntergatherer said:
I am trying to place a SUM and Nz expresion together in a report. The
outcome should be:
Sum the results - works OK
If no results are returned from the Query to the report then show 0
(zero)

I have tried various combinations of =Nz(Sum([data1])) etc but only
get #error when no data is returned. With data returned works fine.

Probably somthing obvious, but I am a nubie!

Many thanks in anticipation

Peter
Click to expand...

When no records are returned by the recordsource query, there's nothing
to Sum, so what you get is an error, not a Null, and Nz() can't fix
that. Try this:

=IIf([HasData],Sum([data1]),0)

HasData is a property of the report that is False if the report's
recordset is empty, True if not.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Top