MS Access VBA Update Field with Null or Empty String

Question

I am trying to update a MS Access table via VBA from a DAO Recordset. The problem is that if one of the field values is null. The code errors out. Is there something like IsNull(rst.Fields("FirstName").value,"") that I can use? I tried using this but it throws the error "Invalid Use of Null". Please help.

strSQL = Update myTable SET myField ='" & rs.Fields("recField").Value & "' where id = 25
db.Execute strSQL

in this instance, rs.Fields("recField").Value is Null

Answer 1

NZ is MS Access's Built-In function to handle nulls

Nz ( variant, [ value_if_null ] )

• • Nz (rst.Fields("FirstName").Value, 0)

Alternatively you could use the VBA IIF statement.

IIf( Expression, TruePart, FalsePart )

To return an Empty string use:

Nz (rst.Fields("FirstName").Value, "") or IIf(rst.Fields("FirstName").Value, "", rst.Fields("FirstName").Value)

If the field that you are updating is numeric then you'll need to return a Zero value if null:

Nz (rst.Fields("FirstName").Value, 0) or IIf(rst.Fields("FirstName").Value, 0, rst.Fields("FirstName").Value)

Answer 2

You can use my CSql function here like:

strSQL = "Update myTable Set myField = " & CSql(rs.Fields("recField").Value) & " Where id = 25"

That will for a value return:

"Update myTable Set myField = 'ValueOfField' Where id = 25"

and for a value of Null:

"Update myTable Set myField = Null Where id = 25"