Query to SUM two columns and consider null as 0 zero.

[aniyahudi]

BH


Hi, I'm building a c# application using Visual Studio 2010, and connecting to Access Database. I'm trying to sum up two columns, but when one of the columns does not have any values, it should be considered as 0 (zero). I've tried the below Query in visual studio 2010, but it gives me an error saying "Undefined function 'IfISNull" in expression." Am i suppose to be adding some function in order to use the below code? Is the function suppose to be in Visual Studio or inside the Access database?

SELECT SUM(IfISNull(AmountDebit)) - SUM(IfIsNull(AmountCredit)) AS Expr1 FROM Ledger GROUP BY PropertyID HAVING (PropertyID = ?)

Thank you so much for your help.
Aron

[June7]

Info about Null http://allenbrowne.com/casu-12.html

Use Nz() function.

[aniyahudi]

BH,
hi thank you for your reply. I've tried the Nz function but I get the same message saying:"Undefined function 'Nz" in expression." by the way, I'm doing this directly the Visual Studio 2010 dataset.Maybe it's the Visual Studio that does not recognize Access functions?

[June7]

Okay, Nz() is an Access/VBA function, even Excel doesn't like it.

Then will have to use the long version with IIf():

Sum(IIf(IsNull([AmountDebit]),0,[AmountDebit]))

[amrut]

Is your SQL working in Access ?

SELECT SUM(IfISNull(AmountDebit)) - SUM(IfIsNull(AmountCredit)) AS Expr1 FROM Ledger GROUP BY PropertyID HAVING (PropertyID = ?)