How do I update Columns that contain NULL values in VBA Access using SQL?

Question

I am trying to update columns which contain Null values. I thought that the bellow code would work but I get syntax error for missing operator in query expression. I can't seems to figure it out. Any help?

Private sub dataUpdate_Click()
Dim SQL As String
On Error GoTo cancelledClicked

SQL = "UPDATE table1 " & _
"SET [Column1] = 1 WHERE [Column1] IS NULL " & _
"SET [Column2] = 0 WHERE [Column2] IS NULL; "

DoCmd.RunSQL SQL

exitDataUpdate:
Exit Sub

ignoreError:
MsgBox Err.Description
Exit Sub

cancelledClicked:
If Err.Number = 2501 Then GoTo exitDataUpdate
If Err.Number <> 2501 Then GoTo ignoreError

Resume Next

End sub

There is only one SET and one WHERE clause in an UPDATE statement. Each can contain multiple columns / conditions, though. -- But if you want to update two different data sets, you need two SQL statements. — Andre
– Andre, Commented Jun 28, 2018 at 11:42
Andre, thanks for the direction. I have 25 columns and curently, it seems like I need 25 SQL statements. Is that correct? — Rof
– Rof, Commented Jun 28, 2018 at 13:44
The important thing is the data set = the rows you want to update = the WHERE clause. If they are different, you need separate statements. — Andre
– Andre, Commented Jun 28, 2018 at 13:56
Sometimes the data set is complete with the required data in which case the data set should not be updated but in some cases the data set has a Null value in some fields it is therefore required that a Null is converted to 0. — Rof
– Rof, Commented Jun 28, 2018 at 14:11

Gordon Linoff · Accepted Answer · 2018-06-28 12:51:43Z

2

Is this the logic you want?

UPDATE table1 
    SET [Column1] = NZ([Column1], 1),
        [Column2] = NZ([Column2], 0)
    WHERE [Column1] IS NULL OR [Column2] IS NULL

Your code has a syntax error.

edited Jun 28, 2018 at 12:51

answered Jun 28, 2018 at 11:08

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Zack Over a year ago

Also, he doesn't have a space after the first null, so it ends up being ...WHERE [Column1] IS NULLSET [Column2]...

Gustav Over a year ago

Gordon, that will update all Column1 fields where Column2 is Null.

Gordon Linoff Over a year ago

@Zack . . . That would be the least of the syntax problems.

Gustav Over a year ago

But coalesce is T-SQL. It should be Nz.

Collectives™ on Stack Overflow

How do I update Columns that contain NULL values in VBA Access using SQL?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related