vba 测试字段是否为空 ms 访问
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15282405/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Testing if fields are null ms access
提问by Katana24
Im trying to create a SQL statement in MS Access that will look for blank fields in a table. Say, for example, a patients last name is missing - this query should pick this up.
我试图在 MS Access 中创建一个 SQL 语句，该语句将在表中查找空白字段。例如，假设缺少患者姓氏 - 此查询应选择此信息。
How would I go about doing this? I'm able to determine how to do it for the first record but for an entire table is proving difficult
我该怎么做呢？我能够确定如何为第一条记录执行此操作，但事实证明对于整个表来说很困难
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
采纳答案by user2088176
More elegantly:
更优雅：
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
回答by HansUp
Looks to me like your code would do what you want if you use MoveNextbefore Loop. I make a few other minor changes in this version.
在我看来，如果您MoveNext之前使用Loop. 我在这个版本中做了一些其他的小改动。
Dim Rst As DAO.recordSet
Dim f As DAO.Field
Dim db As DAO.Database
'Current Record set
Set db = CurrentDb
Set Rst = db.OpenRecordset("tblWebMeetingData", dbOpenTable, dbOpenSnapshot)
With Rst
Do While Not .EOF
For Each f In .Fields
If IsNull(f.Value) Then
MsgBox "Field Name: " & f.Name
End If
Next
.MoveNext
Loop
End With
Rst.Close
However, in the question you said you want to "create a SQL statement in MS Access that will look for blank fields in a table". But, instead of a query you showed us VBA code which inspects a DAO.Recordset. I'm not sure what you really want.
但是，在您说的问题中，您要“在 MS Access 中创建一个 SQL 语句，该语句将在表中查找空白字段”。但是，您向我们展示的不是查询，而是检查DAO.Recordset. 我不确定你真正想要什么。
回答by Mike
Why not just:
为什么不只是：
Public Sub CheckNull(FieldName as String)
Dim rs as DAO.RecordSet
Set rs = CurrentDB.OpenRecordset("SELECT IDField FROM tblMyTable WHERE " & FieldName & " Is Null")
If rs.eof then exit sub
Do until rs.Eof
debug.print rs!IDField
rs.movenext
Loop
End Sub
This should in code lookup all records where FieldNameis Null and then print the results to the immediate window where you pass the FieldName as a string.
这应该在代码中查找FieldName为 Null 的所有记录，然后将结果打印到您将 FieldName 作为字符串传递的即时窗口。
