Null 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;
 
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.
 
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")

Jim Burke in Novi said:
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 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...
Click to expand...
 
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.

Jerry Anderson said:
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")

Jim Burke in Novi said:
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 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...
Click to expand...
Click to expand...
 
Back
Top