Find all columns with null values for every record based ID field

Help Please, How can I show the result where all column values are null? For example, I have a table like this: (There are 51 columns/fields in this table), I want to check each record and if the records have complete null for the ID, then that record should be the output.

ID	Col1	Col2	Col25	Col50
1	ABC	Null	JAC	Null
2	Null	EFG	PQR	Null
3	Null	Null	Null	Null
4	HIJ	JQR	POT	MNO

And I want my result to look like below:

ID	Col1	Col2	Col25	Col50
3	Null	Null	Null	Null

CodePudding user response：

You can concatenate the fields:

Select * From YourTable
Where (Col1 & Col2 & ... & Col50) Is Null

Using VBA and DAO to check a single Id:

Public Function CheckNulls(ByVal Id As Long) As Boolean

    Dim Records As DAO.Recordset
    Dim Field   As DAO.Field
    
    Dim AllNull As Boolean
    
    Set Records = CurrentDb.OpenRecordset("Select * From YourTable Where Id = " & Id & "")
    
    If Records.RecordCount = 1 Then
        For Each Field In Records.Fields
            If Field.Name = "Id" Then
                Set Field = Nothing
            ' Check for Null:
            ElseIf Not IsNull(Field.Value) Then
                Exit For
            ' Check for zero length string:
            ElseIf Nz(Field.Value) <> "" Then
                Exit For
            End If
        Next
    End If
    Records.Close
    
    AllNull = (Field Is Nothing)
    
    CheckNulls = AllNull
    
End Function

Used in a query:

SELECT 
    Id, CheckNulls([Id]) AS AllNull
FROM 
    YourTable
WHERE 
    CheckNulls([Id]) = True;