count null values in record-row 2

[emikoo]

Hi,

I would like to count the number of empty (null) fields in a recordset. For example:
Select a,b,c....,z fom Mytable ;

suppose "b" and "d" are empty/null then statement should return 2, if only "c" is null then statement should return "1".

Any help would be appreciated.

Thxs,

EK

 

[cmmrfrds]

Select sum(iif(a=null,1,0)) as cola,
sum(iif(b=null,1,0)) as colb,
sum(iif(c=null,1,0)) as colc,
sum(iif(d=null,1,0)) as cold
from Mytable

 

[r937]

tsk, cmmrfrds, nothing=null

you must have meant

Select sum(iif(isnull(a),1,0)) as cola,
sum(iif(isnull(b),1,0)) as colb,
sum(iif(isnull(c),1,0)) as colc,
sum(iif(isnull(d),1,0)) as cold
from Mytable

rudy

 

[cmmrfrds]

You are right that is good OR
Select sum(iif(a is null,1,0)) as cola,
sum(iif(b is null,1,0)) as colb,
sum(iif(c is null,1,0)) as colc,
sum(iif(d is null,1,0)) as cold
from Mytable

 

[PaulBricker]

If you have many fields to count, it might be easier to use this function.
Function NullCount(ParamArray FieldValues()) As Variant
Dim lngMax As Long
Dim varArg As Variant

lngMax = 0

For Each varArg In FieldValues
If IsNull(varArg) Then
lngMax = lngMax + 1
End If
Next

NullCount = lngMax
End Function

Then in the query put this

myCount:NullCount(Field1,Field2,Field3,....Fieldn)

This should return the number of nulls in the record.

Paul