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

Question

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

Gustav · Accepted Answer · 2022-10-26T14:54:48.150

1

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;

edited Oct 26 '22 at 14:54

answered Oct 23 '22 at 12:08

Gustav

53,498
7
29
55

I would like to loop through each column instead of adding each 50 column to SQL – Ibnu Marjan Oct 23 '22 at 12:25
Well, then do so. But you stated, that you would list all records matching the criterium, and the fastest method for that is to use a query. – Gustav Oct 23 '22 at 12:59
I am sorry, but because the number of columns is high, I would like to have VBA code, instead a query. I am looking for the VBA code where first it should check all the columns (50) automatically and give the result if all the columns are null for an ID. And also a number of records in this table is around 50000 – Ibnu Marjan Oct 23 '22 at 13:08
Then loop the _Fields_ collection of the recordset. – Gustav Oct 23 '22 at 15:51
I am new to VBA can you please guide me on this – Ibnu Marjan Oct 25 '22 at 12:12
Yes. See extended answer, please. – Gustav Oct 25 '22 at 13:14
Am I interpreted wrongly, the above code gives all records, and I think it should loop through all the col1-col50 and if all are empty then it should list the ID as in my first post. – Ibnu Marjan Oct 26 '22 at 06:55
That it will do. If not, at least one field is not _Null_ (empty). – Gustav Oct 26 '22 at 07:10
But I need only the records in which all the fields (col1 to col50) are Null (empty) – Ibnu Marjan Oct 26 '22 at 10:44
Then filter as shown. See edited answer, please. – Gustav Oct 26 '22 at 11:53
I am really sorry, It gives empty records, can you please check the two tables in my question, I need only the records in which all the fields (col1 to col50) are Null (empty) and with each ID. in my sample table ID=3 of the col1-col50 are null values, I need those records only. – Ibnu Marjan Oct 26 '22 at 12:48
Then, perhaps you have _zero length string_ values. See expanded answer, please. – Gustav Oct 26 '22 at 14:55
Same no records showing – Ibnu Marjan Oct 26 '22 at 19:05
Sorry, code is verified, so your data are not what you think they are. – Gustav Oct 26 '22 at 19:53
Sorry, I even check with the sample table as shown in my question. but the same result. – Ibnu Marjan Oct 27 '22 at 09:50
Well, it works here, so double-check everything. – Gustav Oct 27 '22 at 10:26
Thanks for your support I got workaround from your code – Ibnu Marjan Oct 27 '22 at 14:25
Great! Then please mark as answered, if you feel so, and/or post your revised code. – Gustav Oct 27 '22 at 14:32

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

1 Answers1