如何在Access中检查空值？

I am new to Access. I have a table full of records. I want to write a function to check if any id is null or empty. If so, I want to update it with xxxxx. The check for id must be run through all tables in a database. Can anyone provide some sample code?

我是Access的新手。我有一张满是记录的桌子。我想编写一个函数来检查是否有任何id为null或为空。如果是这样,我想用xxxxx更新它。必须在数据库中的所有表中运行对id的检查。有人能提供一些示例代码吗?

3 个解决方案

#1

I'm not sure if you are going to be able to find all tables in the database with Access SQL. Instead, you might want to write up some VBA to loop through the tables and generate some SQL for each table. Something along the lines of:

我不确定您是否能够使用Access SQL查找数据库中的所有表。相反,您可能希望编写一些VBA来遍历表并为每个表生成一些SQL。有点像:

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

#2

Check out the Nz() function. It leaves fields unaltered unless they're null, when it replaces them by whatever you specify.

查看Nz()函数。它会保持字段不变,除非它们为null,当它用你指定的任何内容替换它们时。

For reasonable numbers and sizes of tables, it can be quicker to just

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

open them
sort by each field in turn

依次按每个字段排序

inspect for null values and replace manually

检查空值并手动替换

It's good practice to find out where the nulls are coming from and stop them - give fields default values, use Nz() on inputs. And have your code handle any nulls that slip through the net.

最好找出空值的来源并停止它们 - 给出字段默认值,在输入上使用Nz()。并让你的代码处理任何滑过网络的空值。

#3

-1

I'm calling it the UpdateFieldWhereNull Function, and shown is a Subroutine which calls it (adapted from http://www.aislebyaisle.com/access/vba_backend_code.htm)

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

It updates all tables in the DbPath parameter (not tested, handle with care):

它会更新DbPath参数中的所有表(未经过测试,请小心处理):

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

#1