Sums with Null Values

  • Thread starter Thread starter Jasper Recto
  • Start date Start date
J

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
 
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.
 
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
 
Does that hold true for when its in a DSum equation or if you Do a Sum
query?



KARL DEWEY said:
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


Jasper Recto said:
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
Click to expand...
Click to expand...
 
I do not know about in DSum - try it.
Yes in a Sum query.
--
KARL DEWEY
Build a little - Test a little


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



KARL DEWEY said:
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


Jasper Recto said:
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
Click to expand...
Click to expand...
Click to expand...
 
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






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


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



KARL DEWEY said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.
 
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



Jasper Recto said:
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






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


Jasper Recto said:
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
Click to expand...
Click to expand...
Click to expand...
 
THANKS!

Good to know!

Jasper
Michel Walsh said:
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



Jasper Recto said:
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






KARL DEWEY said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top