vba - 测试字段是否为空 ms Access

Question

我正在尝试在 MS Access 中创建一个 SQL 语句来查找表中的空白字段。比方说，患者的姓氏丢失了——这个查询应该选择这个。

我该怎么做呢？我能够确定如何为第一条记录做这件事，但对于整个表格来说很难

Dim Rst As recordSet
Dim f As Field

'Current Record set
Set Rst = CurrentDb.OpenRecordset("tblWebMeetingData")

'Holds current fields data
Dim fieldData

'With Rst
    'Do Until Rst.EOF
       For Each f In Rst.Fields
            If IsNull(f.Value) Then
                MsgBox ("Field Name: " & f.Name)
            End If
       Next
    'Loop
'End With

Rst.Close

Answer 1

更优雅:

Dim i As String
i = "tblWebMeetingData"
Dim j As Integer
Dim rst As Recordset

' For each Field in the table
For j = 0 To CurrentDb.TableDefs(i).Fields.Count - 1
    ' Return the number of lines that are null
    Set rst = CurrentDb.OpenRecordset("SELECT count(*) FROM " & CurrentDb.TableDefs(i).Name & " WHERE " & CurrentDb.TableDefs(i).Fields(j).Name & " IS NULL")
    rst.MoveFirst
    ' Check if it's more than one
    If rst.Fields(0).Value > 0 Then
        MsgBox CurrentDb.TableDefs(i).Fields(j).Name
    End If
Next