Count Fields with data.

Hello,

Can someone help me with a VBA to count how many fields have a value.

I've had a search but can't find what I am after.

In crystal reports I can do it using count(myarray(field1,field2,field3) but not sure how I'd do this in Access.

Thanks.

It can be done but working across the fields within records is often a messy process in Access because it is designed to work with the values in a field across multiple records. If you were wanting to count the number of records with a value in a particular field it would be very simple.

Having a need to count the number of fields with a value in a record often suggests the data is not normalized. Do you have the option of restructuring the data?

I don't have the option restructuring the data unfortunately.

I am using this to give me a Minimum and I was wondering if this could be changed to do a count, not sure how I'd do it thought:


Function Minimum(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Single
Dim currentVal As Variant

' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)

' Cycle through each value from the row to find the smallest.
For I = 0 To UBound(FieldArray)
If FieldArray(I) < currentVal Then
currentVal = FieldArray(I)
End If
Next I

' Return the minimum value found.
Minimum = currentVal

You can use a Totals Query to count like this:
That is what you will put under each field but you need to Group By one field so just use:
So use Group By under this alias field and set the other counts to Expression.
You can then use a recordset to loop through this query to get a count of those fields that don't return 0.