SQL UPDATE Statement (A2K)

Topic

New Reply

I need to update a field to 0 in a linked table where that field is Null.

The field in the table is NOT static, each month a field is added to this table, i.e. Mar08Panel, Apr08Panel, May08Panel.

I don’t want the user to have to go into a query and pull down the newest field added and update that field, so I thought about using an SQL UPDATE statement but am struggling to get it to work.

Dim mysql As String
Dim sMth, sYR, sField As String

sMth = Format(DateSerial(Year(Date), Month(Date) - 2, 1), "mmm")
sYR = Format(DateSerial(Year(Date), Month(Date) - 2, 1), "yy")
sField = sMth & sYR & "Panel"

DoCmd.SetWarnings False

'mysql = "UPDATE tblPCPPanelSize Set" & sField & " = isnull(" & sField & ", 0)"
mysql = "UPDATE tblPCPPanelSize SET " & sField & "  = 0 WHERE " & sField & " ISNull"
Debug.Print mysql
DoCmd.RunSQL mysql

DoCmd.SetWarnings True

However, I continue to get “Missing Operator” error msg. I’ve tried to change the syntax but still get the msg. Can someone please identify to me what I’m missing?

Reply | Quote

Replies

Instead of ISNull use Is Null (with a space between Is and Null):
mysql = "UPDATE tblPCPPanelSize SET " & sField & " = 0 WHERE " & sField & " Is Null"

IsNull(…) is a VBA function that returns True or False

… Is Null is used in SQL to test the value of a field or expression for being Null.

BTW, instead of

sMth = Format(DateSerial(Year(Date), Month(Date) - 2, 1), "mmm")
sYR = Format(DateSerial(Year(Date), Month(Date) - 2, 1), "yy")
sField = sMth & sYR & "Panel"

you can use

sField = Format(DateSerial(Year(Date), Month(Date) - 2, 1), "mmmyy") & "Panel"

Reply | Quote

Thanks Hans for both suggestions, they work beautifully…

Reply | Quote