Number of empty values.

[shamal]

Welcome..
How to calculate empty fields using the Visual Basic function.

I used this function in a query, but I want it in a function using Visual Basic
D_Null: IIf(IsNull([sn]);1;0)+IIf(IsNull([an]);1;0)+IIf(IsNull([en]);1;0)+IIf(IsNull([tn]);1;0)+IIf(IsNull([mn]);1;0)+IIf(IsNull([kn]);1;0)

 

[arnelgp]

put in a Module:

Public Function fnCountEmpty(ParamArray pArray() As Variant) As Integer
    Dim ret As Integer
    Dim v As Variant
    For Each v In pArray
        ret = ret + IsNull(v)
    Next
    fnCountEmpty = ret * -1
End Function

change your Query to:

D_Null: fnCountEmpty([sn], [an], [en], [tn], [mn], [kn])

 

[shamal]

put in a Module:

Public Function fnCountEmpty(ParamArray pArray() As Variant) As Integer
    Dim ret As Integer
    Dim v As Variant
    For Each v In pArray
        ret = ret + IsNull(v)
    Next
    fnCountEmpty = ret * -1
End Function

change your Query to:

D_Null: fnCountEmpty([sn], [an], [en], [tn], [mn], [kn])

Thank you
Very excellent function

 

[ebs17]

But you already realize that both variants are workarounds because the starting point is a crosstab and therefore an unnormalized table.
With a previous unpivot you would get a normalized table and could start a simple query.

SELECT
   AttributID,
   n_Field,
   COUNT(*) - COUNT(n_Field) AS EmptyFields
FROM
   TableX
WHERE
   n_Field IN("sn", "an", "en", "tn", "mn", "kn")
GROUP BY
   AttributID,
   n_Field

 

[shamal]

But you already realize that both variants are workarounds because the starting point is a crosstab and therefore an unnormalized table.
With a previous unpivot you would get a normalized table and could start a simple query.

SELECT
   AttributID,
   n_Field,
   COUNT(*) - COUNT(n_Field) AS EmptyFields
FROM
   TableX
WHERE
   n_Field IN("sn", "an", "en", "tn", "mn", "kn")
GROUP BY
   AttributID,
   n_Field

I don't understand your method.

 

[Pat Hartman]

@ebs17 has assumed that the image you posted is not from a table but from a query made with a crosstab. The schema looks unnormalized so that is an obvious conclusion. If ebs is correct and the data is actually normalized, evaluating the data directly rather than crosstabing first is a better solution. However, I don't like the suggested solution since it hardcodes the In() clause. A better solution would be to use a table to control the "IN", especially since in a normalized schema, you shouldn't have empty rows anyway so the hardcoded In() wouldn't work since the empty rows would not exist.

 

[ebs17]

since it hardcodes the In() clause

This hardcoding is also available in the above two variants, so it is just a simple comparison.
You can also design it differently, but you should know the environment.