DSUM with empty tables

  • Thread starter Thread starter Ted
  • Start date Start date
T

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
 
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")
 
Back
Top