cannot sum null; not zero

  • Thread starter Thread starter Rick Stahl
  • Start date Start date
R

Rick Stahl

In a query I am trying to sum currency fields from subqueries. Some of
these fields are blank because no value should be there. Where there is
values in all fields of a given record, summation works fine. However
wherever there is a blank field, no sum is returned. Do I have to make all
the blank fields zero or is there a way I can sum the fields "as is" by
somehow stating somewhere "if null, then equals zero" ? Thank you.
 
SELECT Sum(Nz(FieldName, 0)) AS MySum
FROM Tablename;

The Nz function will substitute the value 0 for a Null value in the field
when doing the sum calculation.
 
Thank you. I understand what you are saying but I am not real familiar with
SQL. I apologize for my ignorance but I only am familiar with using the
"Design View" in Access. Part of the SQL includes:

SELECT [qryHourlyWeeklyNon-TaxDeductions].curMiscDeduction,
[qryHourlyWeeklyNon-TaxDeductions].tblWeeklyHoursAttendance.strComments,
[qryHourlyWeeklyNon-TaxDeductions].curDomRel,
[qryHourlyWeeklyNon-TaxDeductions].curOtherDeductions,
[qryHourlyWeeklyNon-TaxDeductions].[tblNon-TaxDeductions].strComments,
[qryHourlyWeeklyNon-TaxDeductions].[curTotalNon-TaxDeductions],
[curTotalTaxes]+[curTotalNon-TaxDeductions] AS curTotalDeductions

I may need help with the format. Thank you.
 
Thank you. I understand what you are saying but I am not real familiar with
SQL. I apologize for my ignorance but I only am familiar with using the
"Design View" in Access. Part of the SQL includes:

SELECT [qryHourlyWeeklyNon-TaxDeductions].curMiscDeduction,
[qryHourlyWeeklyNon-TaxDeductions].tblWeeklyHoursAttendance.strComments,
[qryHourlyWeeklyNon-TaxDeductions].curDomRel,
[qryHourlyWeeklyNon-TaxDeductions].curOtherDeductions,
[qryHourlyWeeklyNon-TaxDeductions].[tblNon-TaxDeductions].strComments,
[qryHourlyWeeklyNon-TaxDeductions].[curTotalNon-TaxDeductions],
[curTotalTaxes]+[curTotalNon-TaxDeductions] AS curTotalDeductions

I may need help with the format. Thank you.

Ken Snell (MVP) said:
SELECT Sum(Nz(FieldName, 0)) AS MySum
FROM Tablename;

The Nz function will substitute the value 0 for a Null value in the field
when doing the sum calculation.
Click to expand...
Click to expand...

Select ... etc....
Nz([curTotalTaxes],0)+Nz([curTotalNon-TaxDeductions],0) AS
curTotalDeductions

Look up the Nz function in VBA help. It's very useful in other
situations as well.
 
Thank you very much ! It worked nicely.


fredg said:
Thank you. I understand what you are saying but I am not real familiar
with
SQL. I apologize for my ignorance but I only am familiar with using the
"Design View" in Access. Part of the SQL includes:

SELECT [qryHourlyWeeklyNon-TaxDeductions].curMiscDeduction,
[qryHourlyWeeklyNon-TaxDeductions].tblWeeklyHoursAttendance.strComments,
[qryHourlyWeeklyNon-TaxDeductions].curDomRel,
[qryHourlyWeeklyNon-TaxDeductions].curOtherDeductions,
[qryHourlyWeeklyNon-TaxDeductions].[tblNon-TaxDeductions].strComments,
[qryHourlyWeeklyNon-TaxDeductions].[curTotalNon-TaxDeductions],
[curTotalTaxes]+[curTotalNon-TaxDeductions] AS curTotalDeductions

I may need help with the format. Thank you.

Ken Snell (MVP) said:
SELECT Sum(Nz(FieldName, 0)) AS MySum
FROM Tablename;

The Nz function will substitute the value 0 for a Null value in the
field
when doing the sum calculation.
--

Ken Snell
<MS ACCESS MVP>


In a query I am trying to sum currency fields from subqueries. Some of
these fields are blank because no value should be there. Where there
is
values in all fields of a given record, summation works fine. However
wherever there is a blank field, no sum is returned. Do I have to make
all the blank fields zero or is there a way I can sum the fields "as
is"
by somehow stating somewhere "if null, then equals zero" ? Thank you.
Click to expand...
Click to expand...

Select ... etc....
Nz([curTotalTaxes],0)+Nz([curTotalNon-TaxDeductions],0) AS
curTotalDeductions

Look up the Nz function in VBA help. It's very useful in other
situations as well.
Click to expand...
 
Back
Top