505.369.1650 [email protected]

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

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.zip

Making Microsoft Access Work

No matter what your MS Access needs are, Carl can provide live help to make sure you get the solution you need.