SQL Error Help

[RayToddJr]

I have the following SQL:

strSQL = "INSERT INTO taDEFENDANTS(TrusteeDefendantID)" & _
"VALUES(lngTrusteeDefendantID)" & _
"WHERE DefendantID=" & lngDefendantID & ";"

The Problem:

I get the following error:

Run-time error 3137:
Missing semicolon ( at end of SQL statement

What am I missing that prevents the code from seeing the semicolon that is
there?

Sorry if this is double posted. My system crashed right after I clicked
post and never saw the first posted.

 

[John Spencer]

What you are missing is that an INSERT statement adds records. Therefore
there is no WHERE clause that applies with this syntax.

Also you need the value of lngTrusteeDefendantID inserted into the SQL string
and not a reference to the variable - which won't be recognized

Plus you were missing spaces whenever you had a new line.

strSQL = "INSERT INTO taDEFENDANTS(TrusteeDefendantID)" & _
" VALUES(" & lngTrusteeDefendantID & ")"

OR perhaps you want an UPDATE query and not an insert query?
strSQL = "Update taDEFENDANTS(TrusteeDefendantID)" & _
" Set TrusteeDefendantID =" & lngTrusteeDefendantID & " & _
" WHERE DefendantID=" & lngDefendantID

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

 

[RayToddJr]

Thanks John.

Ray.

What you are missing is that an INSERT statement adds records. Therefore
there is no WHERE clause that applies with this syntax.

Also you need the value of lngTrusteeDefendantID inserted into the SQL string
and not a reference to the variable - which won't be recognized

Plus you were missing spaces whenever you had a new line.

strSQL = "INSERT INTO taDEFENDANTS(TrusteeDefendantID)" & _
" VALUES(" & lngTrusteeDefendantID & ")"

OR perhaps you want an UPDATE query and not an insert query?
strSQL = "Update taDEFENDANTS(TrusteeDefendantID)" & _
" Set TrusteeDefendantID =" & lngTrusteeDefendantID & " & _
" WHERE DefendantID=" & lngDefendantID

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

.