CODEWITHSUNDEEP
ms-accessvba
BICube
BICube

Reputation: 4681

Replacing null values for multiple columns in a table with a constant value

I am trying to run a script that replaces all null values in a table with a constant value in MS Access 2007. I am fully aware that this can be done using an update query. But the requirement that I need to do that for all columns in one click. I figured that find/replace can do this, but still I would rather minimize any manual work in the process.

I tried using this code but when I try to run it, it just doesn't do anything. It doesn't even trigger an error. 

Sub replacingnulls()

Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("testtable", dbOpenTable)

If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst
    Do Until rs.EOF = True
            rs.Edit

        For Each fld In rs.Fields

            If IsNull(fld.Value) Then
                fld.Value = 555

            End If
        Next fld
        rs.MoveNext
  Loop
Else
    MsgBox ("There are no records")
End If

rs.Close

End Sub

Upvotes: 1

Views: 1188

Answers (1)

HansUp
HansUp

Reputation: 97100

Call rs.Update to save the changes you made to the current record before moving to the next record.

Next fld
rs.Update ' <-- add this
rs.MoveNext

Instead of looping through the table rows and then through each field within each row, you could execute one UPDATE per field. 

Const cstrTable As String = "testtable"
Dim db As DAO.Database
Dim fld As DAO.Field
Dim tdf As DAO.TableDef
Dim strUpdate As String

Set db = CurrentDb
Set tdf = db.TableDefs(cstrTable)
For Each fld In tdf.Fields
    ' Access will complain if you attempt an UPDATE on an autonumber field,
    ' so skip field with dbAutoIncrField attribute
    If Not ((fld.Attributes And dbAutoIncrField) = dbAutoIncrField) Then
        strUpdate = "UPDATE [" & cstrTable & "] SET [" & fld.Name & _
            "] = 555 WHERE [" & fld.Name & "] Is Null;"
        'Debug.Print strUpdate
        db.Execute strUpdate, dbFailOnError
    End If
Next

Beware that code will attempt to replace all Nulls with 555 regardless of the field's datatype. If all the fields are plain numbers, that may be fine. However a Date/Time field with Null would become Jul 8 1901 00:00:00. OTOH, your recordset approach would do the same thing ... so if it's OK there, it should be OK here, too.

Upvotes: 3

Related Questions