Error 3144 syntax error in UPDATE statement

  • Thread starter Thread starter Ralph
  • Start date Start date
R

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
 
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
 
Ralph said:
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
Click to expand...


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.
 
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
 
Ralph said:
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
Click to expand...

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