1

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

Sasha Egania
  • 53
  • 1
  • 5
  • Please edit to include the entire method so we can see what your code is trying to achieve – 0liveradam8 Aug 16 '17 at 20:21
  • 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 Aug 16 '17 at 20:35

2 Answers2

0

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)

  • Don't know enough Access-VBA, but (a) wouldn't NULL evaluate to `False` in an `If`/`IIf` (because it isn't equal to `True`)? (b) would the OP be able to use `...SET myField =" & IIf(IsNull(rst.Fields("recField").Value), "NULL", "'" & rst.Fields("FirstName").Value) & "'") & " where ...` if they actually wanted to set the `myField` field to a NULL if the `recField` field was NULL? (just in case they want to promulgate the NULLs rather than changing it to an empty string or a zero) – YowE3K Aug 16 '17 at 20:53
  • [Application.Nz Method (Access)](https://msdn.microsoft.com/en-us/vba/access-vba/articles/application-nz-method-access) uses `IIf(IsNull(varFreight), 0, varFreight) ` as it's example but you are correct that Null will evaluate as false. So I guess the `IsNull()` function isn't needed. –  Aug 16 '17 at 21:09
  • Don't you mean the `IsNull()` function **is** needed? `IIf(varFreight, 0, varFreight)` would return `0` if `varFreight` evaluated to `True` and would return `varFreight` if `varFreight` evaluated to `False` (which it will do if it is `Null`). Argh - too early in the morning to be thinking of logical variables! – YowE3K Aug 16 '17 at 21:12
  • You would use `Null` without the quotation marks (e.g. `UPDATE Table1 SET Table1.T1 = Null;`) –  Aug 16 '17 at 21:13
  • I think that is what my suggestion in (b) creates as the SQL, so I guess I got that bit right :) :) – YowE3K Aug 16 '17 at 21:14
  • `IIf(varFreight, varFreight, 0)` would be comparable to `IIf(IsNull(varFreight), 0, varFreight)`. I would still use the `IsNull` to clarify the statement. –  Aug 16 '17 at 21:17
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/152100/discussion-between-yowe3k-and-thomas-inzina). – YowE3K Aug 16 '17 at 21:19
0

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"
Gustav
  • 53,498
  • 7
  • 29
  • 55