Hi Guys & Gals,

Real simple, I have a record that has 7 currency fields in it and I'm
trying to run a query to calculate the total, the following query works
well:

SELECT ref, date, tenants, property, (amount1 + amount2 + amount3 +
amount4 + amount5 + amount6 + amount7) AS total FROM receipt

Only trouble is, if any of the 7 amount fields are NULL then it doesn't
return the total, it's just blank.

Is there an easy way to make this query calculate NULL as 0.00 on those
currency fields without changing the table so all amount fields are 0.00
if they are not set?

Thanks,

Phil.

"Phil Read" <phil@vizualweb.com> wrote in message
news:E2D63557-CFFF-4411-8016-9104AB0A0BD1@news.elevatesoft.com...
>
> Only trouble is, if any of the 7 amount fields are NULL then it doesn't
> return the total, it's just blank.
>

SELECT IF(F1 = NULL THEN CAST(0.00 AS DECIMAL) ELSE F1) + IF(F2 = NULL THEN
CAST(0.00 AS DECIMAL) ELSE F2) AS TOTAL FROM TABLE

Robert

Phil

Wrap each of the fields in COALESE (or however you spell it) in the sql


Roy Lambert

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:1C33459C-64BA-4C01-97F0-F74D88CCA313@news.elevatesoft.com...
> Phil
>
> Wrap each of the fields in COALESE (or however you spell it) in the sql
>

How would you use COALESCE for this query?

Robert

Robert


select (COALESCE(amount1,0) + COALESCE(amount2,0)) As Maxed from params


Roy Lambert