Error 3144 - Syntax error in UPDATE Statement

  • Thread starter Thread starter Dusty
  • Start date Start date
D

Dusty

I am trying to run an UPDATE sql query in a module. I get the 3144
error but I'm not sure where the error is. Any help would be
appreciated.

Thanks

Function UpdateData()

Dim RS As DAO.Recordset
Dim DB As DAO.Database
Dim qdf As QueryDef
Dim strQryName As String
Dim CtyName As String
Dim FirstLine As Integer
Dim LastLine As Integer
Dim cty As String
Dim strSQL As String

Set DB = CurrentDb
Set RS = CurrentDb.OpenRecordset("T_CountyLineNumbers")

With RS
.MoveFirst
Do While Not .EOF

CtyName = ![County]
FirstLine = ![FirstRow]
LastLine = ![LastRow]

strSQL = "UPDATE T_0002_QFR145RA_Phase_2 SET
(((T_0002_QFR145RA_Phase_2.County)=""" & CtyName & """))" & _
"WHERE (((T_0002_QFR145RA_Phase_2.ID)>=" & FirstLine & ")
And ((T_0002_QFR145RA_Phase_2.ID)<=" & LastLine & "));"

DoCmd.RunSQL strSQL

' Set qdf = DB.CreateQueryDef("qryUpdateData", strSQL)
'
' strQryName = "qryUpdateData"
'
' DB.Execute "qryUpdateData"
'
' DB.QueryDefs.Delete qdf.Name
.MoveNext
Loop
End With

RS.Close
Set DB = Nothing

End Function
 
Try inserting a space before WHERE in your query.

strSQL = "UPDATE T_0002_QFR145RA_Phase_2 " & _
" SET T_0002_QFR145RA_Phase_2.County=""" & CtyName & """" & _
" WHERE T_0002_QFR145RA_Phase_2.ID>=" & FirstLine & _
" And T_0002_QFR145RA_Phase_2.ID<=" & LastLine & ";"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Try inserting a space before WHERE in your query.

strSQL = "UPDATE T_0002_QFR145RA_Phase_2 " & _
" SET T_0002_QFR145RA_Phase_2.County=""" & CtyName & """" & _
" WHERE T_0002_QFR145RA_Phase_2.ID>=" & FirstLine & _
" And T_0002_QFR145RA_Phase_2.ID<=" & LastLine & ";"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.




I am trying to run an UPDATE sql query in a module. I get the 3144
error but I'm not sure where the error is. Any help would be
appreciated.

Function UpdateData()
Click to expand...
Dim RS As DAO.Recordset
Dim DB As DAO.Database
Dim qdf As QueryDef
Dim strQryName As String
Dim CtyName As String
Dim FirstLine As Integer
Dim LastLine As Integer
Dim cty As String
Dim strSQL As String
Click to expand...
Set DB = CurrentDb
Set RS = CurrentDb.OpenRecordset("T_CountyLineNumbers")
Click to expand...
With RS
.MoveFirst
Do While Not .EOF
Click to expand...
CtyName = ![County]
FirstLine = ![FirstRow]
LastLine = ![LastRow]
Click to expand...
strSQL = "UPDATE T_0002_QFR145RA_Phase_2 SET
(((T_0002_QFR145RA_Phase_2.County)=""" & CtyName & """))" & _
"WHERE (((T_0002_QFR145RA_Phase_2.ID)>=" & FirstLine & ")
And ((T_0002_QFR145RA_Phase_2.ID)<=" & LastLine & "));"
Click to expand...
DoCmd.RunSQL strSQL
Click to expand...
' Set qdf = DB.CreateQueryDef("qryUpdateData", strSQL)
'
' strQryName = "qryUpdateData"
'
' DB.Execute "qryUpdateData"
'
' DB.QueryDefs.Delete qdf.Name
.MoveNext
Loop
End With
Click to expand...
RS.Close
Set DB = Nothing
Click to expand...
End Function- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

That worked perfectly! Thanks alot
 
I am trying to run an UPDATE sql query in a module. I get the 3144
error but I'm not sure where the error is. Any help would be
appreciated.

Thanks

Function UpdateData()

Dim RS As DAO.Recordset
Dim DB As DAO.Database
Dim qdf As QueryDef
Dim strQryName As String
Dim CtyName As String
Dim FirstLine As Integer
Dim LastLine As Integer
Dim cty As String
Dim strSQL As String

Set DB = CurrentDb
Set RS = CurrentDb.OpenRecordset("T_CountyLineNumbers")

With RS
.MoveFirst
Do While Not .EOF

CtyName = ![County]
FirstLine = ![FirstRow]
LastLine = ![LastRow]

strSQL = "UPDATE T_0002_QFR145RA_Phase_2 SET
(((T_0002_QFR145RA_Phase_2.County)=""" & CtyName & """))" & _
"WHERE (((T_0002_QFR145RA_Phase_2.ID)>=" & FirstLine & ")
And ((T_0002_QFR145RA_Phase_2.ID)<=" & LastLine & "));"
Click to expand...

Try setting a breakpoint on the line above (mouseclick in the vertical bar to
the left of the code window to get a red dot). Run the code and see what is
actually getting put into strSQL.

You can type

?strSQL

in the Immediate window (type ctrl-G to make it visible) after the statement
executes, and copy and paste it to the SQL window of a new Query to see if it
can be run there.

John W. Vinson [MVP]
 
please try my problem at an earliest
I am trying to run an UPDATE sql query in a module. I get the 3144
error but I'm not sure where the error is. Any help would be
appreciated.

Thanks

Function UpdateData()

Dim RS As DAO.Recordset
Dim DB As DAO.Database
Dim qdf As QueryDef
Dim strQryName As String
Dim CtyName As String
Dim FirstLine As Integer
Dim LastLine As Integer
Dim cty As String
Dim strSQL As String

Set DB = CurrentDb
Set RS = CurrentDb.OpenRecordset("T_CountyLineNumbers")

With RS
.MoveFirst
Do While Not .EOF

CtyName = ![County]
FirstLine = ![FirstRow]
LastLine = ![LastRow]

strSQL = "UPDATE T_0002_QFR145RA_Phase_2 SET
(((T_0002_QFR145RA_Phase_2.County)=""" & CtyName & """))" & _
"WHERE (((T_0002_QFR145RA_Phase_2.ID)>=" & FirstLine & ")
And ((T_0002_QFR145RA_Phase_2.ID)<=" & LastLine & "));"

DoCmd.RunSQL strSQL

' Set qdf = DB.CreateQueryDef("qryUpdateData", strSQL)
'
' strQryName = "qryUpdateData"
'
' DB.Execute "qryUpdateData"
'
' DB.QueryDefs.Delete qdf.Name
.MoveNext
Loop
End With

RS.Close
Set DB = Nothing

End Function
On Monday, July 09, 2007 2:55 PM John Spencer wrote:
Try inserting a space before WHERE in your query.

strSQL = "UPDATE T_0002_QFR145RA_Phase_2 " & _
" SET T_0002_QFR145RA_Phase_2.County=""" & CtyName & """" & _
" WHERE T_0002_QFR145RA_Phase_2.ID>=" & FirstLine & _
" And T_0002_QFR145RA_Phase_2.ID<=" & LastLine & ";"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

news:[email protected]...
Click to expand...
Click to expand...
 
please try my problems at an earliest
I am trying to run an UPDATE sql query in a module. I get the 3144
error but I'm not sure where the error is. Any help would be
appreciated.

Thanks

Function UpdateData()

Dim RS As DAO.Recordset
Dim DB As DAO.Database
Dim qdf As QueryDef
Dim strQryName As String
Dim CtyName As String
Dim FirstLine As Integer
Dim LastLine As Integer
Dim cty As String
Dim strSQL As String

Set DB = CurrentDb
Set RS = CurrentDb.OpenRecordset("T_CountyLineNumbers")

With RS
.MoveFirst
Do While Not .EOF

CtyName = ![County]
FirstLine = ![FirstRow]
LastLine = ![LastRow]

strSQL = "UPDATE T_0002_QFR145RA_Phase_2 SET
(((T_0002_QFR145RA_Phase_2.County)=""" & CtyName & """))" & _
"WHERE (((T_0002_QFR145RA_Phase_2.ID)>=" & FirstLine & ")
And ((T_0002_QFR145RA_Phase_2.ID)<=" & LastLine & "));"

DoCmd.RunSQL strSQL

' Set qdf = DB.CreateQueryDef("qryUpdateData", strSQL)
'
' strQryName = "qryUpdateData"
'
' DB.Execute "qryUpdateData"
'
' DB.QueryDefs.Delete qdf.Name
.MoveNext
Loop
End With

RS.Close
Set DB = Nothing

End Function
On Monday, July 09, 2007 2:55 PM John Spencer wrote:
Try inserting a space before WHERE in your query.

strSQL = "UPDATE T_0002_QFR145RA_Phase_2 " & _
" SET T_0002_QFR145RA_Phase_2.County=""" & CtyName & """" & _
" WHERE T_0002_QFR145RA_Phase_2.ID>=" & FirstLine & _
" And T_0002_QFR145RA_Phase_2.ID<=" & LastLine & ";"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

news:[email protected]...
Click to expand...
Click to expand...
 
You are missing a space before the WHERE clause. I've stripped out the
unneeded parentheses. My assumption is that FirstLine and LastLine are numbers.

strSQL = "UPDATE T_0002_QFR145RA_Phase_2" & _
" SET T_0002_QFR145RA_Phase_2.County=""" & CtyName & """" & _
" WHERE T_0002_QFR145RA_Phase_2.ID>=" & FirstLine & _
" And T_0002_QFR145RA_Phase_2.ID<=" & LastLine

Good troubleshooting technique when a constructed query does not work is to
temporarily add in a line

Debug.Print strSQL

That will print the SQL string in the VBA immediate window, where you can
examine it for errors. Also, you can copy and paste the SQL string into a new
query and try to run it there from the SQL view. Often the error message is
better AND sometimes the cause of the error is highlighted.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top