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.

 

[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.

 

[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,