count fields in a record that meet criteria

[JEA]

I need to wite a function that will count the number of field in a record that are not null. I don't want it to count all fields that are not null, just the fields specified in the function's code. The record's index is passed to the function as an argument.

The solutions that I think I've found use SQL. My background's in java, I'm new to VBA but it's within my comfort zone. SQL however is way outside my comfort zone. If the solution is in VBA that'd be great as I don't want to have to learn a completly alien language.

Thanks in advance.

 

[JEA]

been thinking...

could I use a For Each loop such as,

storing the list of fields that I want to check (and count each 'not null' value) in an array then:

For Each 'fieldName in an array'

    If (look up current fieldName from For loop, and record index from function args)  is not null then 

         n++

    End If

Next

this would mean n contains the number of fields in the array that weren't null. I have no idea how to implement this in VBA though.

 

[JEA]

I think I've solved it:

Dim n As Integer
Dim fieldList(6) As String
Dim I As Integer

fieldList(0) = "[S1Type]"
fieldList(1) = "[S2Type]"
fieldList(2) = "[S3Type]"
fieldList(3) = "[S4Type]"
fieldList(4) = "[S5Type]"
fieldList(5) = "[S6Type]"
fieldList(6) = "[S7Type]"

n = 0

For I = 0 To 6

    If Not (IsNull(DLookup(fieldList(I), "hours", "[DateWorked] = #" & dateWorked & "#"))) Then
    n = n + 1
    End If
    
Next I

Sorry about a wasted thread, maybe someone else will find it useful though.

 

[rsmonkey]

Alternately a simpler way would have been to create 2

 If Not Then, Do While

recordsets and just loop em through an update statement..