sum query 1

[tuzojazz]

Hi:

I have this query

SELECT SUM(Total) FROM CargoD Where Folio=100

there are no records in the table "CargoD" Where Folio=100 and I get a null result

how could I write the query in order to get 0 instead of null?

Thanks!!

 

[unclejimbob]

What about something like:

SELECT COALESCE(SUM(Total),0) FROM CargoD Where Folio=100

Note that the above is untested - I am not at my machine to check it so just had a read through the BNF for ANSI SQL 92 and a quick look at what other people have done to get the job done...

 

[tuzojazz]

thanks unclejimbob

but COALESCE does not work in access

What can I do?

 

[PHV]

work in access
SELECT Nz(SUM(Total),0) FROM CargoD Where Folio=100

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tuzojazz]

Thanks phv!

It works!!