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

Please edit to include the entire method so we can see what your code is trying to achieve — 0liveradam8
Provide code. Are you setting VBA string variables? Only variant type variable can hold Null. I don't allow empty strings in tables but if you want: Nz(rst!FirstName, ""). — June7

Unknown Unknown · Accepted Answer · 2017-08-16T20:37:39

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)

MS Access VBA Update Field with Null or Empty String

2 Answers