Select and check if nth column from recordset is empty in access vba

Question

I have recordset in Access from table with 25 columns. I need to write a makro where program select column 10, checks if it is empty or not, and then do the same check for every 3rd column till the end of the recordset

I wrote below makro, I tried to count number of columns in recordset and then set variable from 10th column to the end of the recordset as below

Public Sub calculations()
Dim Tactical  As dao.Database
Dim rsvol As dao.Recordset
Dim i As Long

Set Tactical = CurrentDb
Set rsvol = Tactical.OpenRecordset("Calculations Table")

Debug.Print rsvol.Fields.Count
For i = 10 To rsvol.Fields.Count

Answer 1

This will return the empty fields after field 10 that are null.
It assumes you only have a single field set as the Primary Key. If you've got more than one field set as PK then you'll need to split the PrimKey variable.

On a side note it looks like you're trying to use a database table the same way you would a spreadsheet. They're not really comparable.

Some SQL like below would return records where certain fields are null.

SELECT  *
FROM    Table1
WHERE   Field10 Is Null OR Field12 Is Null or Field15 Is Null Or Field18 Is Null

This code will search through the table and print in the immediate window which fields are null and what the PK value is for that record.

Public Sub Test()

    Dim Tactical As DAO.Database
    Dim rsVol As DAO.Recordset
    
    Set Tactical = CurrentDb
    Set rsVol = Tactical.OpenRecordset("Table1")
    
    Dim x As Long
    With rsVol
        If Not .BOF And Not .EOF Then
        
            Dim PrimKey As String
            PrimKey = GetPrimaryKey(rsVol.Name)
        
            .MoveFirst
            Do While Not .BOF And Not .EOF
                For x = 10 To .Fields.Count
                    If IsNull(.Fields(x - 1)) Then
                        Debug.Print "PK: " & .Fields(PrimKey) & " - " & .Fields(x - 1).Name & " is empty"
                    End If
                Next x
                .MoveNext
            Loop
        End If
        .Close
    End With

End Sub

Public Function GetPrimaryKey(TableName As String) As String

    With CurrentDb
        Dim td As DAO.TableDef
        Set td = .TableDefs(TableName)
        
        Dim idxLoop As Index
        For Each idxLoop In td.Indexes
            If idxLoop.Primary Then
                GetPrimaryKey = Mid(idxLoop.Fields, 2)
                Exit For
            End If
        Next idxLoop
    End With

End Function