Replied 31 Dec 2002 15:28:58

Hi mitchelt,

does the column contain nulls or zero's?

Digga

Sharing Knowledge Saves Valuable Time!!!

Replied 31 Dec 2002 15:37:00

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!

Replied 31 Dec 2002 18:16:22

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

Replied 31 Dec 2002 18:20:36

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!

Replied 01 Jan 2003 10:17:53

<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

Replied 01 Jan 2003 16:48:33

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

Replied 01 Jan 2003 16:56:57

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

Replied 01 Jan 2003 17:05:16

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

Replied 01 Jan 2003 18:19:54

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

Replied 02 Jan 2003 05:13:42

Owen,

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

Thanks,

Mitch

Replied 15 Oct 2007 19:30:29

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

Replied 10 Jul 2008 07:54:24

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,

Replied 11 Jul 2008 18:59:49

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.