Sum Incorrect in Report

[VAM]

I am a new user, so bear with me. I am developing a use and cost report from
data that I need to download from a vendor. The data is in csv format, which
I convert to Excel worksheets and import into Access 2003. When I use the
sum function in the report footer for the TotalCharges field, I get cost that
are way out of whack. I realize this is a simple problem, but the
=sum([TotalCharges]) formula is just not working. I have done this just a
ton of times in different ways. Many thanks for any assistance you can give.

 

[Klatuu]

If any the field in any row in your recordset contains a Null value, it will
cause havoc with the Sum. The way to do that is use the Nz function to
convert Nulls to 0:

=Sum(Nz([TotalCharges],0))

The Nz function has two arguments. The first is the value to test. The
second is the value to return if the first argument is Null. It will return
the current value of the first argument if it is not null and the value of
the second argument if it is.

 

[VAM]

This did not seem to work, but thanks for the tip. I have logged this into my
troubleshooting directory. Any other suggestions? I'm banging my head on
the desk here.
--
V

If any the field in any row in your recordset contains a Null value, it will
cause havoc with the Sum. The way to do that is use the Nz function to
convert Nulls to 0:

=Sum(Nz([TotalCharges],0))

The Nz function has two arguments. The first is the value to test. The
second is the value to return if the first argument is Null. It will return
the current value of the first argument if it is not null and the value of
the second argument if it is.

--
Dave Hargis, Microsoft Access MVP

I am a new user, so bear with me. I am developing a use and cost report from
data that I need to download from a vendor. The data is in csv format, which
I convert to Excel worksheets and import into Access 2003. When I use the
sum function in the report footer for the TotalCharges field, I get cost that
are way out of whack. I realize this is a simple problem, but the
=sum([TotalCharges]) formula is just not working. I have done this just a
ton of times in different ways. Many thanks for any assistance you can give.

 

[Klatuu]

Then the query is not correctly constructed and returning an incorrect value.
You can test this be opening the query directly and reviewing the data.
OR, there is an issue with the report. It could be filtering the data
incorrectly or the scope of the Sum is not correct.
--
Dave Hargis, Microsoft Access MVP

This did not seem to work, but thanks for the tip. I have logged this into my
troubleshooting directory. Any other suggestions? I'm banging my head on
the desk here.
--
V

If any the field in any row in your recordset contains a Null value, it will
cause havoc with the Sum. The way to do that is use the Nz function to
convert Nulls to 0:

=Sum(Nz([TotalCharges],0))

The Nz function has two arguments. The first is the value to test. The
second is the value to return if the first argument is Null. It will return
the current value of the first argument if it is not null and the value of
the second argument if it is.

--
Dave Hargis, Microsoft Access MVP

I am a new user, so bear with me. I am developing a use and cost report from
data that I need to download from a vendor. The data is in csv format, which
I convert to Excel worksheets and import into Access 2003. When I use the
sum function in the report footer for the TotalCharges field, I get cost that
are way out of whack. I realize this is a simple problem, but the
=sum([TotalCharges]) formula is just not working. I have done this just a
ton of times in different ways. Many thanks for any assistance you can give.

 

[Rick Brandt]

If any the field in any row in your recordset contains a Null value,
it will cause havoc with the Sum. The way to do that is use the Nz
function to convert Nulls to 0:

Nulls do not cause such problems when aggregating. Only when used as
operands in expressions. When aggregating with Sum(), Max(), Count(), etc..
Nulls are simply ignored. The exception being Count(*).

 

[Klatuu]

That has not been my experience, Rick. Two days ago I had to chage a query
that was returning all zeros where data were in some rows and some contained
Nulls. With the Nz wrapped around the field name, I got the values returned
correctly.

 

[Fred]

is TotalCharges a field or is it the name of a control where you already did
a caluclation? I don't think that you can do the latter in which case you'd
need to rewrite that other calculation instead of using TotalCharges.

 

[VAM]

TotalCharges is a calculating field specific to the report. The report pulls
a certain cost center from a main table.
--
V

is TotalCharges a field or is it the name of a control where you already did
a caluclation? I don't think that you can do the latter in which case you'd
need to rewrite that other calculation instead of using TotalCharges.

I am a new user, so bear with me. I am developing a use and cost report from
data that I need to download from a vendor. The data is in csv format, which
I convert to Excel worksheets and import into Access 2003. When I use the
sum function in the report footer for the TotalCharges field, I get cost that
are way out of whack. I realize this is a simple problem, but the
=sum([TotalCharges]) formula is just not working. I have done this just a
ton of times in different ways. Many thanks for any assistance you can give.