Null value not adding in expression

  • Thread starter Thread starter Jerry Anderson
  • Start date Start date
J

Jerry Anderson

I have a query (SQL below) which works fine, with one exception:
When adding 2 values [Salaries-Actual] and [Other-Actual], if either value
is null, the resulting addition returns null. What am I doing wrong?????

SELECT BudgetTitles.BudgetUnitTitle, Sum(ActualSalaries.TotalCost) AS
[Salaries-Actual], Sum(ActualOtherCosts.ExtendedCost) AS [Other-Actual],
[Salaries-Actual]+[Other-Actual] AS [Combined-Actual]
FROM (BudgetTitles LEFT JOIN ActualSalaries ON
BudgetTitles.BudgetUnitTitle=ActualSalaries.BudgetUnit) LEFT JOIN
ActualOtherCosts ON BudgetTitles.BudgetUnitTitle=ActualOtherCosts.CostCenter
GROUP BY BudgetTitles.BudgetUnitTitle;
 
Jerry said:
I have a query (SQL below) which works fine, with one exception:
When adding 2 values [Salaries-Actual] and [Other-Actual], if either value
is null, the resulting addition returns null. What am I doing wrong?????

SELECT BudgetTitles.BudgetUnitTitle, Sum(ActualSalaries.TotalCost) AS
[Salaries-Actual], Sum(ActualOtherCosts.ExtendedCost) AS [Other-Actual],
[Salaries-Actual]+[Other-Actual] AS [Combined-Actual]
FROM (BudgetTitles LEFT JOIN ActualSalaries ON
BudgetTitles.BudgetUnitTitle=ActualSalaries.BudgetUnit) LEFT JOIN
ActualOtherCosts ON BudgetTitles.BudgetUnitTitle=ActualOtherCosts.CostCenter
GROUP BY BudgetTitles.BudgetUnitTitle;
Click to expand...


That's the definition of how + interacts with Null

Presumably, you would like the Nulls treated as zeros. to
do that you can use the Nz function:

Nz([Salaries-Actual], 0) + Nz([Other-Actual], 0)
 
Back
Top