DSUM with empty tables

[Ted]

This is a problem with use of DSUM against an empty (contains no
records) table.

I have an unbound report that shows summarised data (using DSUM) from
7 tables .
Here's an example of the control source for a text box in the report:
=DSum("[01 distribution]","tblTrustDistribution_01Prioryear")

It is not uncommon (for a variety of reasons) for one or more of those
7 tables to be empty - that is, contain zero records. In that case,
the DSUM returns no visible output (not even a zero).

If there are no records, then I'd at least like a zero value.

I've tried Nz() - as in
=DSum("nz([01 distribution])","tblTrustDistribution_01Prioryear"), and
=nz(DSum("[01 distribution]","tblTrustDistribution_01Prioryear"))
I've also tried IIF, as in
=iif(DSum("[01 distribution]","tblTrustDistribution_01Prioryear")=Null,0,DSum("[01
distribution]","tblTrustDistribution_01Prioryear"))

So far, nothing works.

Any suggestions?

Ted

 

[Fredg]

Regarding:
=nz(DSum("[01 distribution]","tblTrustDistribution_01Prioryear"))

Close but no cigar :-(

Try...
=nz(DSum("[01 distribution]","tblTrustDistribution_01Prioryear"),0)

You can even write some text if you wish:
=nz(DSum("[01 distribution]","tblTrustDistribution_01Prioryear"),"No
records")