如何检查Access中的空值?

我不确定您是否能用Access SQL找到数据库中的所有表。相反,您可能需要编写一些VBA来遍历表并为每个表生成一些SQL。大致如下:

update TABLE set FIELD = 'xxxxxx' where ID is null

对于合理数量和大小的表,可以更快地

• 打开它们
• 按每个字段依次排序
• 检查空值并手动替换

找出空值的来源并停止它们是一个很好的实践-给出字段的默认值,对输入使用Nz()。让你的代码处理任何通过网络的空值。

我称之为 updateFieldWhere空 函数,显示的是调用它的子例程( 改编自 http://www.aislebyaisle.com/access/vba_backend_code.htm )

它会更新 参数( ):

Function UpdateFieldWhereNull(DbPath As String, fieldName as String, newFieldValue as String) As Boolean
    'This links to all the tables that reside in DbPath,
    '  whether or not they already reside in this database.
    'This works when linking to an Access .mdb file, not to ODBC.
    'This keeps the same table name on the front end as on the back end.
    Dim rs As Recordset

        On Error Resume Next

    'get tables in back end database
        Set rs = CurrentDb.OpenRecordset("SELECT Name " & _
                                        "FROM MSysObjects IN '" & DbPath & "' " & _
                                        "WHERE Type=1 AND Flags=0")
        If Err <> 0 Then Exit Function

    'update field in tables
        While Not rs.EOF
            If DbPath <> Nz(DLookup("Database", "MSysObjects", "Name='" & rs!Name & "' And Type=6")) Then

                'UPDATE the field with new value if null
                DoCmd.RunSQL "UPDATE " & acTable & " SET [" & fieldName & "] = '" & newFieldValue & "' WHERE [" & fieldName & "] IS NULL"

            End If
            rs.MoveNext
        Wend
        rs.Close

        UpdateFieldWhereNull = True
End Function


Sub CallUpdateFieldWhereNull()
    Dim Result As Boolean

    'Sample call:
    Result = UpdateFieldWhereNull("C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb", "ID", "xxxxxx")
    Debug.Print Result
End Sub