Sum a field with null value

[iccsi]

I would like to sum a field in the report.
The field get null result sometimes and I want to get sum for the
field result is not null.

I have following code in the control source of the text box, but the
report failed.

=IIf(IsNull([Raw1]),0,Sum([Raw1]))

Any suggestions for this issue?

Your help is great appreciated,

 

[Access Developer]

How about SUM(NZ(Raw1)) ? That seems likely what you need.

Larry Linson, Microsoft Office Access MVP

 

[iccsi]

Thanks a millions for helping,

Yes, My query uses "", but not null.
How can I use sum for ""?

Your help is great appreciated,

I would like to sum a field in the report.
The field get null result sometimes and I want to get sum for the
field result is not null.

I have following code in the control source of the text box, but the
report failed.

=IIf(IsNull([Raw1]),0,Sum([Raw1]))

If the field is really Null in some records, all you need
is:
=Sum(Raw1)

The important thing to be aware of is that all aggregate
functions (Count, Sum. Max. etc), except Count(*), ignore
Null values. Null values will not affect the total, nor
will they get in the way with something like Avg(fld).

If Sum(Raw1) does not calculate the total, then you have
something other than Null in those records.

 

[John Spencer]

You might try an expression like the following which will convert any string
that looks like a valid number expression into a number value and all other
value into null.

Sum(IIF(IsNumeric([Raw1]),Val([Raw1]),Null))


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Thanks a millions for helping,

Yes, My query uses "", but not null.
How can I use sum for ""?

Your help is great appreciated,

I would like to sum a field in the report.
The field get null result sometimes and I want to get sum for the
field result is not null.

I have following code in the control source of the text box, but the
report failed.

=IIf(IsNull([Raw1]),0,Sum([Raw1]))

If the field is really Null in some records, all you need
is:
=Sum(Raw1)

The important thing to be aware of is that all aggregate
functions (Count, Sum. Max. etc), except Count(*), ignore
Null values. Null values will not affect the total, nor
will they get in the way with something like Avg(fld).

If Sum(Raw1) does not calculate the total, then you have
something other than Null in those records.

 

[Marshall Barton]

I don't understand what that means. The Raw1 field must be
a number type field or the Sum function can not work and a
number type field can not have a text value of any kind, not
even an empty string.
--
Marsh
MVP [MS Access]

Yes, My query uses "", but not null.
How can I use sum for ""?

I would like to sum a field in the report.
The field get null result sometimes and I want to get sum for the
field result is not null.

I have following code in the control source of the text box, but the
report failed.

=IIf(IsNull([Raw1]),0,Sum([Raw1]))

If the field is really Null in some records, all you need
is:
=Sum(Raw1)

The important thing to be aware of is that all aggregate
functions (Count, Sum. Max. etc), except Count(*), ignore
Null values. Null values will not affect the total, nor
will they get in the way with something like Avg(fld).

If Sum(Raw1) does not calculate the total, then you have
something other than Null in those records.

 

[iccsi]

Thanks for the message,
The data source of the report is an union query.

I union different tables in to one union query.
The fields are not the same number, I use "" to fill fields which
table does not have the field.

Now, I found solution for this, I use 0 instead of "" in my union
query then =sum(Raw1) works fine now.

Thanks again for helping,

I don't understand what that means. The Raw1 field must be
a number type field or the Sum function can not work and a
number type field can not have a text value of any kind, not
even an empty string.
--
Marsh
MVP [MS Access]

Yes, My query uses "", but not null.
How can I use sum for ""?

iccsi wrote:

I would like to sum a field in the report.
The field get null result sometimes and I want to get sum for the
field result is not null.

I have following code in the control source of the text box, but the
report failed.

=IIf(IsNull([Raw1]),0,Sum([Raw1]))


If the field is really Null in some records, all you need
is:
=Sum(Raw1)

The important thing to be aware of is that all aggregate
functions (Count, Sum. Max. etc), except Count(*), ignore
Null values. Null values will not affect the total, nor
will they get in the way with something like Avg(fld).

If Sum(Raw1) does not calculate the total, then you have
something other than Null in those records.

 

[Marshall Barton]

The data source of the report is an union query.

I union different tables in to one union query.
The fields are not the same number, I use "" to fill fields which
table does not have the field.

Now, I found solution for this, I use 0 instead of "" in my union
query then =sum(Raw1) works fine now.

In most situations, Null should be a better choice than 0.
At least, all the Aggregate functions are correct when using
Null. And with Null you can tell the difference between a
missing value and a real value of 0.

If you have a situation where you want a form/report text
box to display 0 instead of nothing for Null values, just
set the text box's Format property to a custom format like:
0;;;"0"

 

[iccsi]

Thanks for the message,
I tried nulll first, but it seems MS Access does not accept following
union query.

select field1, null from table1
union
select field1, field2 from table2

If MS Access support above query then I will choose null to satisfy my
report data source and resolve my report issue.

I got "query too complex..." error message when I use above query, so
I used 0 instead.

Thanks again for helping,

 

[Marshall Barton]

That may be because Access can not determine the Data Type
of a column with Null. Try reversing the order of the two
Select statements:

select field1, field2 from table2
union
select field1, null from table1

Be sure that you really need to use UNION. If you do not
have duplicate records that you want to suppress, use UNION
ALL

The names of the columns in the result of a union query are
the names from the first Select clause so, if you want to
use aliases, specify them in the first Select statement, but
not in the others.