I'm trying to create a calculated field which will be the average of two other fields in the same query. In some cases one or other (or both) of the fields may be empty. If only one is Null I want the value of the other field to show (i.e. the average of 1 value), and obviously if both are Null then the result is Zero.
Average: IIf( Is Not Null And  Is Not Null,((+)/2),(2006]+))
I hoped that by adding the two fields in the FALSE part I would end up with the correct value, i.e. Integer + 0 = Integer
Unfortunately, Access doesn't seem to like adding an integer and a zero value and returns an empty cell.
It should only be empty if both 2006 AND 2007 are zero
thanks in advance