Hi,

I have this select statement:

SELECT SUM(est_cost) AS sumest_cost
FROM main_costs WHERE status <> 'Closed'

It's part of a larger select statement, but this is the problem section.

When I TEST the statement it returns the value NULL, instead of 0 .

All the info is on an Access 2000 database and the fields "est_cost" and
"actual_cost" is set to number.

Can anyone help with getting the returned value to be 0 ?


The full select statement is:

SELECT (SELECT SUM(est_cost) AS sumest_cost
FROM main_costs WHERE (status <> 'Closed')) +
(SELECT SUM(actual_cost) AS sumactual_cost FROM main_costs WHERE (status =
'Closed'))
FROM main_costs AS estimated_cost


Thanks!

Mitch

Hi mitchelt,

does the column contain nulls or zero's?

Digga

Sharing Knowledge Saves Valuable Time!!!

Hi Digga,

Actually, the columns do not contain nulls or zeros.

The problem is that the first part of the select statement is looking for contracts with the status of anything BUT Closed. The problem comes up when all the contracts have a status of Closed.

If it does not find any, it should be ' 0 ' ZERO, but it comes back as Null.

Thanks!

Mitch


When in doubt...reboot!

Make the default value of the field 0 in the database, should solve your problem.

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

oeastwick,

Thanks but it is not a problem with a default, it is a SUM problem.

It is "summing" the est_cost field if the status is NOT closed.

Thanks,

Mitch

When in doubt...reboot!

<i>It is "summing" the est_cost field if the status is NOT closed.</i>

You've specified that:

SELECT SUM(est_cost) AS sumest_cost
FROM main_costs WHERE (status &lt;&gt; 'Closed')

Haven't you?

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

Yes, but it is part of a larger "formula".

If it gets a NULL anywhere in the formula SQL automatically makes the end result NULL.

SELECT <font color=red>(SELECT SUM(est_cost) AS sumest_cost
FROM main_costs WHERE (status &lt;&gt; 'Closed'))</font id=red> +
(SELECT SUM(actual_cost) AS sumactual_cost FROM main_costs WHERE (status =
'Closed'))
FROM main_costs AS estimated_cost

I can't see the whole bicture, but surely what you want is:

SELECT (<font color=red>SELECT SUM(est_cost) AS sumest_cost
FROM main_costs WHERE (status = 'Closed')</font id=red> +
(SELECT SUM(actual_cost) AS sumactual_cost FROM main_costs WHERE (status =
'Closed'))
FROM main_costs AS estimated_cost

Additionally, if you make the default value of all the costs fields 0 instead of leaving them empty the sum will work as there won't be any NULL values in the database which mess up the SUM functions.


Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

Edited by - oeastwick on 01 Jan 2003 17:00:03

The problem is not the SUM of empty fields, all the fields have a 0 in them as a default.

The problem is that sometimes if everything is going smoothly, the STATUS of all the contracts will be CLOSED.

If the status of all the contracts are CLOSED, then this "part" of the statement will return a NULL:

<font color=red>(SELECT SUM(est_cost) AS sumest_cost
FROM main_costs WHERE (status &lt;&gt; 'Closed')) </font id=red>

and totally mess up the rest of the statement.

Thanks,

Mitch

Right, with you now.

You might have to do it in 2 hits:

Recordset1.Source = "SELECT SUM(est_cost) AS sumest_cost FROM main_costs WHERE (status &lt;&gt; 'Closed')"

Recordset2.Source = "SELECT SUM(actual_cost) AS sumactual_cost FROM main_costs WHERE (status = 'Closed')"

varSumest_cost = Recordset1.Fields.Item("sumest_cost".Value

varSumactual_cost = Recordset2.Fields.Item("sumactual_cost".Value

If varSumest_cost &lt;&gt; NULL Then <font color=red>'&lt;---- *</font id=red>
varEstimated_cost = varSumest_cost + varSumactual_cost
Else
varEstimated_cost = varSumactual_cost
End If

<font color=red>*</font id=red> Not sure about this, you'll have to check it out, maybe:

If varSumest_cost &lt;&gt; 0

Or I have used this workaround before:

varTest = varSumest_cost & "zzzz"
If varTest = "zzzz" Then
etc......


Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

Edited by - oeastwick on 01 Jan 2003 18:20:26

Owen,

Interesting solution...I'll try it ASAP!!!

Thanks,

Mitch

Did you try to use the ISNULL option? Something like this:
SELECT ISNULL(SUM(col_name), 0) FROM table_name

TEST IT.
if you use mssql try with:
SELECT ISNULL(SUM(new_val),0) FROM TABLE_NAME

IF use Oracle try with:
SELECT NVL(SUM(new_val),0) FROM TABLE_NAME

Good luck,

The way I got around this same problem was to split up the query in subqueries and use UNION ALL between them, then the other queries do not get "squashed". Each subquery may return NULL is separated. The only problem is that the queries that return NULL will not return anything to a report and consequently may mess up spacing etc on reports.

There has to be a better way than to set all enpty fields to 0.