为每个基于记录的ID字段查找所有具有空值的列-Access VBA

Question

帮助，如何显示所有列值为null的结果？例如，我有一个这样的表：(这个表中有51列/字段(，我想检查每个记录，如果这些记录的ID完全为null，那么该记录应该是输出。

>第25列第50列em>空>FGNull>POT>td style="ext-align:center；">MNO

ID	Col1	Col2
1	ABC	JAC	空
2	空	PQR
3	Null	Null
4	HIJ	JQR

您可以连接字段：

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

使用VBA和DAO检查单个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

用于查询：

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