Sums with Null Values

[Jasper Recto]

I have a query that has a column that adds the records from each previous
column.

However, if one of those columns is blank, than the total is blank.

Why does that happen?

Thanks,
Jasper

 

[KARL DEWEY]

Null plus a value equals Null.
Use the Nz function like this --
Expr1: Nz([Field1], 0) + Nz([Field2], 0)Nz([Field3], 0)
This changes the nulls to zero and that value can be used in math unless you
try to divide by it.

 

[Bryan in Bakersfield]

A blank column is a NULL value. You cannot add a NULL value to anything.

You just need to add a check for NULL values and handle them.

Not sure what your doing but add an IIF with an ISNULL check - something like

IIF ( IsNull([YourFieldName]) , 0, <your current value> )

This way IF your field has a null value it will use a 0 for the value, else
use the value after the second comma. You would put whatever you are
currently using for the value in place of <your current value>.
[YourFieldName] would be the name of your field (hence the name).

Bryan

 

[Jasper Recto]

Does that hold true for when its in a DSum equation or if you Do a Sum
query?

Null plus a value equals Null.
Use the Nz function like this --
Expr1: Nz([Field1], 0) + Nz([Field2], 0)Nz([Field3], 0)
This changes the nulls to zero and that value can be used in math unless
you
try to divide by it.
--
KARL DEWEY
Build a little - Test a little

I have a query that has a column that adds the records from each previous
column.

However, if one of those columns is blank, than the total is blank.

Why does that happen?

Thanks,
Jasper

 

[KARL DEWEY]

I do not know about in DSum - try it.
Yes in a Sum query.
--
KARL DEWEY
Build a little - Test a little

Does that hold true for when its in a DSum equation or if you Do a Sum
query?

Null plus a value equals Null.
Use the Nz function like this --
Expr1: Nz([Field1], 0) + Nz([Field2], 0)Nz([Field3], 0)
This changes the nulls to zero and that value can be used in math unless
you
try to divide by it.
--
KARL DEWEY
Build a little - Test a little

I have a query that has a column that adds the records from each previous
column.

However, if one of those columns is blank, than the total is blank.

Why does that happen?

Thanks,
Jasper

 

[Jasper Recto]

So are you saying that if I have a table that has cost information and I
plan on doing any sumation on any fields I have to check for null values on
all my queries?

Thanks!
Jasper

I do not know about in DSum - try it.
Yes in a Sum query.
--
KARL DEWEY
Build a little - Test a little

Does that hold true for when its in a DSum equation or if you Do a Sum
query?

Null plus a value equals Null.
Use the Nz function like this --
Expr1: Nz([Field1], 0) + Nz([Field2], 0)Nz([Field3], 0)
This changes the nulls to zero and that value can be used in math
unless
you
try to divide by it.
--
KARL DEWEY
Build a little - Test a little


:

I have a query that has a column that adds the records from each
previous
column.

However, if one of those columns is blank, than the total is blank.

Why does that happen?

Thanks,
Jasper

 

[schasteen]

Another option is to set the default value for thos fields to zero, so in the
future you don't have to worry about having nulls. Then run an update query
to set all of the nulls to zero.

 

[Michel Walsh]

No, in a SUM, and in VBA-DSUM( ... ), the nulls are logically removed
before the aggregate occurs. In fact, the null are always (logically)
automatically removed for any aggregate EXCEPT for COUNT(*) since *
indicates a record, not a value from a particular field.



Vanderghast, Access MVP

So are you saying that if I have a table that has cost information and I
plan on doing any sumation on any fields I have to check for null values
on all my queries?

Thanks!
Jasper

I do not know about in DSum - try it.
Yes in a Sum query.
--
KARL DEWEY
Build a little - Test a little

Does that hold true for when its in a DSum equation or if you Do a Sum
query?



Null plus a value equals Null.
Use the Nz function like this --
Expr1: Nz([Field1], 0) + Nz([Field2], 0)Nz([Field3], 0)
This changes the nulls to zero and that value can be used in math
unless
you
try to divide by it.
--
KARL DEWEY
Build a little - Test a little


:

I have a query that has a column that adds the records from each
previous
column.

However, if one of those columns is blank, than the total is blank.

Why does that happen?

Thanks,
Jasper

 

[Jasper Recto]

THANKS!

Good to know!

Jasper

No, in a SUM, and in VBA-DSUM( ... ), the nulls are logically removed
before the aggregate occurs. In fact, the null are always (logically)
automatically removed for any aggregate EXCEPT for COUNT(*) since *
indicates a record, not a value from a particular field.



Vanderghast, Access MVP

So are you saying that if I have a table that has cost information and I
plan on doing any sumation on any fields I have to check for null values
on all my queries?

Thanks!
Jasper

I do not know about in DSum - try it.
Yes in a Sum query.
--
KARL DEWEY
Build a little - Test a little


:

Does that hold true for when its in a DSum equation or if you Do a Sum
query?



Null plus a value equals Null.
Use the Nz function like this --
Expr1: Nz([Field1], 0) + Nz([Field2], 0)Nz([Field3], 0)
This changes the nulls to zero and that value can be used in math
unless
you
try to divide by it.
--
KARL DEWEY
Build a little - Test a little


:

I have a query that has a column that adds the records from each
previous
column.

However, if one of those columns is blank, than the total is blank.

Why does that happen?

Thanks,
Jasper