Error 3144 syntax error in UPDATE statement

[Ralph]

Why am I getting an error message? Here is the code, I have Access 2003.
I am trying to select the appropriate table to update using a command button
on a form. The form has the reference objects "grpSampleMonth and "txtCalYr".

Private Sub btnTransFlag_Click()
Dim SQL As String
Dim FNSmo As String


FNSmo = MonthName(Forms.frmLoadToFNS.grpSampleMonth, True) & txtCalYr


SQL = "UPDATE tbl1FNS&FNSmo & SET TransFlag = ""0"" WHERE TransFlag Is
Null "

DoCmd.RunSQL SQL
End Sub

 

[Ralph]

I tried this too -

Private Sub btnTransFlag_Click()
Dim SQL As String
Dim FNSmo As String

FNSmo = MonthName(Forms.frmLoadToFNS.grpSampleMonth, True) & txtCalYr

SQL = "UPDATE tbl1FNS & FNSmo & _
SET TransFlag = ""0"" & _
WHERE TransFlag Is Null "

DoCmd.RunSQL SQL
End Sub

 

[Dirk Goldgar]

Why am I getting an error message? Here is the code, I have Access 2003.
I am trying to select the appropriate table to update using a command
button
on a form. The form has the reference objects "grpSampleMonth and
"txtCalYr".

Private Sub btnTransFlag_Click()
Dim SQL As String
Dim FNSmo As String


FNSmo = MonthName(Forms.frmLoadToFNS.grpSampleMonth, True) & txtCalYr


SQL = "UPDATE tbl1FNS&FNSmo & SET TransFlag = ""0"" WHERE TransFlag Is
Null "

DoCmd.RunSQL SQL
End Sub

The table name can't be constructed like that when the SQL statement is
executed. However, since you are building the SQL statement as a string,
you can build the correct name into the string:

SQL = "UPDATE tbl1FNS" & FNSmo & _
" SET TransFlag = ""0"" WHERE TransFlag Is Null "

Is TransFlag really a text field? It's a bit odd that you are setting it to
the text value "0" instead of the numeric value 0.

 

[Ralph]

TransFlag field in the table is a text field. I am still getting the 3144
error with the following code change:

Private Sub btnTransFlag_Click()

Dim SQL As String
Dim FNSmo As String

FNSmo = MonthName(Forms.frmLoadToFNS.grpSampleMonth, True) & txtCalYr

SQL = "UPDATE tbl1FNS" & FNSmo & _
"SET TransFlag = ""0"" WHERE TransFlag Is Null "

DoCmd.RunSQL SQL

End Sub

 

[Dirk Goldgar]

TransFlag field in the table is a text field. I am still getting the 3144
error with the following code change:

Private Sub btnTransFlag_Click()

Dim SQL As String
Dim FNSmo As String

FNSmo = MonthName(Forms.frmLoadToFNS.grpSampleMonth, True) & txtCalYr

SQL = "UPDATE tbl1FNS" & FNSmo & _
"SET TransFlag = ""0"" WHERE TransFlag Is Null "

DoCmd.RunSQL SQL

End Sub

That is not the code I posted. In the code I posted, there was a space
between the opening quote and the keyword SET:

 

[Ralph]

Thank you this works now. I really appreciate your help and quick response.
Ralph