How can I check for null values in Access?

Question

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?

Answer 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:

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

Answer 2

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)

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

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

Answer 3

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

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.