how to check null in query if i use access

[andypower]

hello my self avi and i m developing bank application in vb 6 and ms access and ado2.1
i want to find out sum of amount from Receiptvoucher table.
but when there r no any record it return NULL and ineed to check that explicitly after executing query. i dont want to check out that explicitly. i want to do something that if there are no any records then query it self shoukd return 0.
i used IIf(isnull(sum(amount),0,sum(amount)) but it gives me error.
in sql i can use COALESCE() function in query or i can use Case Select statement in query but access doesnt support that what can i do plz help me
my query
select sum(Amount) from Receiptvoucher
it irretates me lots......................


and plz tell me instead of case select what can i use in access. plzzzzzzzzzzzz

 

[vidru]

Nz is the equivalent of COALESCE in Access:

SELECT MySum = SUM(Nz(Amount, 0))
FROM Table

-dave

 

[fredericofonseca]

What error do you get?

The following works, although I not sure if the result is the one you expect.

SELECT sum(IIf(IsNull(amount),0,amount)) AS amountx
FROM tbl;

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com