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.
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 support@extramiledata.com.
' 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