>Hi, everyone.
>Would anyone out there be able to tell me why the query below causes a "Syntax error in INSERT INTO statement" when used in VB5 with ADO 2.1? The field types are as follows:
>
>quarter - long
>year - long
>sequence - long
>run_desc - text
>run_comments - memo
>run_date_time - date/time
>status - text
>
>Here's how the code looks:
>
>Dim sSQL As String
>Dim qtr As String, yr As String, desc As String, comments As String
>qtr = CStr(frmMain.cboQuarter.ListIndex + 1)
>yr = CStr(frmMain.cboYear.List(frmMain.cboYear.ListIndex))
>desc = frmMain.txtDesc.Text
>comments = frmMain.txtComments.Text
>sSQL = "INSERT INTO run (quarter,year,sequence,run_desc,run_comments,run_date_time,status) VALUES (" & qtr & "," & yr & ",0,'" & desc & "','" & comments & "','" & Date & "','New')"
>cn.Execute sSQL ' ERROR HAPPENS HERE!
>
>Here's what sSQL looks like at run-time:
>
>INSERT INTO status(quarter,year,sequence,run_desc,run_comments,run_date_time,status) VALUES (1,1952,0,'test','test','09/14/1999','New')
>
>I've tried putting # around the date/time value and also putting nothing around it and the same error still happens.
>
>Any help would be greatly appreciated. Thanks in advance.
>
>Andrew

Try creating your Quarter and Year variables as LONG and then use the conversion function "CLNG(value)". I've had problems inserting this way, and this solution worked for me.

Hugo