Null not adding in expression

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

 

[Jim Burke in Novi]

If you want the values to be 0 in the table itself when there is no specified
value, change the default value in the table to 0. If you need the values in
the table to be null when no value is specified, change the query to:

Nz([Salaries-Actual],0)+Nz([Other-Actual],0) AS [Combined-Actual]

I guess the Sum function handles the Nulls as 0 values but the '+" doesn't.

 

[Jerry Anderson]

Thanks, Jim. That worked great. Now, for the next question:
How do I format the result. I tried putting the following in the criteria
field of the design pane, and it crashed.

=Format(Nz([Combined-Actual],0),"#,##0.00")

If you want the values to be 0 in the table itself when there is no specified
value, change the default value in the table to 0. If you need the values in
the table to be null when no value is specified, change the query to:

Nz([Salaries-Actual],0)+Nz([Other-Actual],0) AS [Combined-Actual]

I guess the Sum function handles the Nulls as 0 values but the '+" doesn't.

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;

 

[Jim Burke in Novi]

It doesn't make sense to format something in the criteria. Criteria should
just be a value to be compared to, you shouldn't be formatting there. What
exactly are you trying to do? If you want the criteria to say it should be
=0, then just put 0 in the criteria field.

Thanks, Jim. That worked great. Now, for the next question:
How do I format the result. I tried putting the following in the criteria
field of the design pane, and it crashed.

=Format(Nz([Combined-Actual],0),"#,##0.00")

If you want the values to be 0 in the table itself when there is no specified
value, change the default value in the table to 0. If you need the values in
the table to be null when no value is specified, change the query to:

Nz([Salaries-Actual],0)+Nz([Other-Actual],0) AS [Combined-Actual]

I guess the Sum function handles the Nulls as 0 values but the '+" doesn't.

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;