DSUM Question

[Brennan]

I am using the DSUM function to pull data from a table. In some cases, there
isn't data for a particular criteria. When that happens, it seems to create
a null value for the entire equation. Is there a way to overcome this?

Here is an example of what I mean:
=DSum("[Net Amount - US]","MFGCosts","[FML Account Code *] = 5000 and [FML
CSUB Account Code *]=1000 and [Accounting Period *]=200804")+DSum("[Net
Amount - US]","MFGCosts","[FML Account Code *] = 5000 and [FML CSUB Account
Code *]=1001 and [Accounting Period *]=200804")+DSum("[Net Amount -
US]","MFGCosts","[FML Account Code *] = 5000 and [FML CSUB Account Code
*]=1002 and [Accounting Period *]=200804")

In this example, the final CSUB of 1002 doesn't have any data for that
particular criteria in the 200804 period. In other periods, it may have data
so I want to make sure I capture it.

Brennan

 

[Allen Browne]

The issue is that any number added to Null results in Null.

Therefore you need to use Nz() around *each* of the DSum() expressions like
this:
=Nz(DSum("[Net Amount - US]","MFGCosts",
"[FML Account Code *] = 5000
and [FML CSUB Account Code *]=1000
and [Accounting Period *]=200804"),0)
+Nz(DSum("[Net Amount - US]","MFGCosts",
"[FML Account Code *] = 5000
and [FML CSUB Account Code *]=1001
and [Accounting Period *]=200804"),0)
+Nz(DSum("[Net Amount - US]","MFGCosts",
"[FML Account Code *] = 5000
and [FML CSUB Account Code *]=1002
and [Accounting Period *]=200804"),0)

For this example, it might be simpler to use a Between or IN operator:
=DSum("[Net Amount - US]", "MFGCosts",
"[FML Account Code *] = 5000
and [FML CSUB Account Code *] IN (1000, 1001, 1002)
and [Accounting Period *]=200804")

 

[Brennan]

Great advice - Thanks for the suggestion with the IN and Between. Works great!