Change Null to 0

[CoffeeGuru]

I have a query that has been returning unexpected (to me) results.
After some interigation I have come to the conclusion that null values are the culprit.

I am trying to average some figures.

In the past I have used this to make sure any null values are passed through as 0

Isnull(CM_DATA.Stock,0) As Stock

In this instance I need to do something similar to this statement

Average(CM_DATA.Stock) As AvgStockHolding


I have tried Average(isnull(CM_DATA.Stock,0)) As AvgStockHolding
but that doess not work

Any Ideas anyone.

Of course a better solution would be to ensure that null values never entered the database..... hindsite is a wonderful thing.

 

[pr2-eugin]

I think the function is Nz not IsNull

Nz() : http://www.techonthenet.com/access/functions/advanced/nz.php
IsNull() : http://www.techonthenet.com/access/functions/advanced/isnull.php