runtime 3137: Missing semicolon at end of statement

[daddypost]

This worked until I added a where clause. "enddate" is a variable I have setup, as is stablename2.
sql = "INSERT INTO weekly_changes (ACTION_USER,ACTION_CODE,action_date,emp_id,change_table)" & _
"VALUES ('" & rscolumns.Fields(0).Value & "','" & rscolumns.Fields(1).Value & "','" _
& Left(rscolumns.Fields(2).Value, 10) & "','" & rscolumns.Fields(icolumn).Value & "','" _
& stablename2 & "') where(rscolumns.Fields(1).Value=enddate));"
End Select
CurrentDb.Execute sql


I didn't have a ; before I added the where clause and it worked fine.
ANY IDEAS??? Thanks

 

[PHV]

The INSERT INTO ... VALUES ... instruction doesn't admit a WHERE clause !

You wanted perhaps something like this ?
If rscolumns.Fields(1).Value = enddate Then
sql = "INSERT INTO weekly_changes (ACTION_USER,ACTION_CODE,action_date,emp_id,change_table)" & _
"VALUES ('" & rscolumns.Fields(0).Value & "','" & rscolumns.Fields(1).Value & "','" _
& Left(rscolumns.Fields(2).Value, 10) & "','" & rscolumns.Fields(icolumn).Value & "','" _
& stablename2 & "')"
CurrentDb.Execute sql
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[daddypost]

That would cause a small problem then wouldn't it. LOL
okay, what I'm actually trying to accomplish is to insert only those records which are in a date range using enddate. I'm assuming I can do a
if rs.field.value between (enddate-6) and (enddate+1) then
blah blah
else
endif.
One more question while you're hopefully here. The date format I'm pulling from sybase is in the form:
2005/01/24-09:23:20:753
1)would I be able to use a 1/24/2005=2005/01/24-09:23:20:753 to return a true statement or will I have to format the date?
2)If I have to format the date, what is the easiest way to do it. Thank you again so much. You've helped me out before and saved my sorry beginning programers a##. Have a good one.

 

[PHV]

Something like this ?
Dim dtFromSybase AS Date
dtFromSybase = CDate(Left(rscolumns.Fields(1).Value, 10))
If dtFromSybase >= (enddate - 6) And dtFromSybase <= (enddate + 1) Then
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[daddypost]

Yeah, unfortunately I didn't realize the sybase field is actually a text field. so I guess I'll have to do a seriel date?