Assigning a value if IIF IsNull is not true.
Posted on 2014-01-21
I have what I hope is a simple question. I have some fields that I would like to add together in a query. Some of the fields may contain null values. I would like any field with a null value to have a value of 0, and all other fields to be assigned a value of 1.
fld1 = null therefore the value assigned would be 0
fld2 = 4 therefore the value assigned would be 1
fld3 = 2 therefore the value assigned would be 1
fld4 = 7 therefore the value assigned would be 1
The desired result of the formula is 3.
I have tried the following, but I get 13 (4+2+7) instead of 3 (1+1+1).
Sum(IIF(Not IsNull([fld1]),1,0)+IIf(Not IsNull([fld2]),1,0)+IIF(Not IsNull([fld3]),1,0)+
Any help would be greatly appreciated.
I am currently using MS Access 2013 on Windows 7.