Susan Harkins
ssharkins at gmail.com
Wed Jan 23 14:04:00 CST 2008
The easiest way to find a syntax error is to define a string variable to hold the evaulated statement and then run a Debug.Print -- it is such a good idea that I never run an evaluated string any other way strSQL = "INSERT INTO tEvictionCasesTemp1 ( UDCaseID, Note ) " & _ > "SELECT " & RS(0) & ", '" & RS(10) & "'" Debug.Print strSQL DoCmd.RunSQL strSQL IF you can't find the error by viewing it in the Immediate window, paste it into a SQL window and run it to get more specific clues as to the real problem. BTW, INSERT INTO needs a FROM clause: INSERT INTO target SELECT * FROM source Using this syntax, target and source may share the same structure. Susan H. DoCmd.RunSQL "INSERT INTO tEvictionCasesTemp1 ( UDCaseID, Note ) " & _ > "SELECT " & RS(0) & ", '" & RS(10) & "'" ----- Original Message ----- From: "Rocky Smolin at Beach Access Software" <rockysmolin at bchacc.com> To: "'Access Developers discussion and problem solving'" <accessd at databaseadvisors.com> Sent: Wednesday, January 23, 2008 2:07 PM Subject: [AccessD] SQL INSERT Question >I have a client who write a lot of his own stuff. Right now he's trying to > insert into an Access table memo field using the following: > > DoCmd.RunSQL "INSERT INTO tEvictionCasesTemp1 ( UDCaseID, Note ) " & _ > "SELECT " & RS(0) & ", '" & RS(10) & "'" > > which generates the error: "Syntax error in INSERT INTO statement" > > RA(n) are the fields in a recordset hooked to SQL database. The fields > RS(0) and RS(10) contain god data (he checked) with no embedded quotes or > apostrophes. Although he says that he needs to be able to > > > The target field Note is a memo field in the Access table. RS(0) has > already > been successfully inserted into tEvictionCasesTemp1. > > What might be causing the syntax error? > > MTIA > > Rocky > > > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com