Need to get a count of the number of columns in the results of a MS Access query that are not Null?  This VBA code will do it.

You can apply the function in a query expression like this:

CountNotNull: CountValues([Field1], [Field2], [Field3], [Field4], [Field5], [Field6])

Code:

Public Function CountValues(Val1, Val2, Val3, Val4, Val5, Val6) As Integer
' This function passes back the number of arguments that had non-null values.
' It passes back a 0 if there was an error.
' Example: CountValues(1, Null, 5, "Joe", Null, Null) = 3

' CountValues() Version 1.0.0
' Copyright © 2009 Extra Mile Data, www.extramiledata.com. 
' For questions or issues, please contact [email protected].
' Use (at your own risk) and modify freely as long as proper credit is given.

On Error GoTo Err_CountValues

    Dim varFinalCount
   
    ' Initialize the final count.
    varFinalCount = 0
   
    ' If an argument is not null, then add a count.
    If Not IsNull(Val1) Then varFinalCount = varFinalCount + 1
    If Not IsNull(Val2) Then varFinalCount = varFinalCount + 1
    If Not IsNull(Val3) Then varFinalCount = varFinalCount + 1
    If Not IsNull(Val4) Then varFinalCount = varFinalCount + 1
    If Not IsNull(Val5) Then varFinalCount = varFinalCount + 1
    If Not IsNull(Val6) Then varFinalCount = varFinalCount + 1
   
    ' Pass back the final count.
    CountValues = varFinalCount

Exit_CountValues:
    Exit Function

Err_CountValues:
    MsgBox Err.Number & " " & Err.Description, vbCritical, "CountValues()"
    CountValues = 0
    GoTo Exit_CountValues

End Function

Download Code:

basCountValues