Failure to Run

[Guest]

A code I have been trying to run gave me the message "Syntax error in INSERT
INTO statement." I haven't had any luck figuring out exactly what is wrong
with the code. Any input would be appretiated.

Option Compare Database
Option Explicit

Dim strSymbol As String
Dim dteLineDate As Date
Dim intRef As Integer
Dim strSQL As String
Dim dbs As Database
Dim rst As Recordset

Private Sub CopyData()
On Error GoTo Err_CopyData

strSymbol = InputBox("Enter Symbol Code:")
intRef = -63
dteLineDate = DMin("[DATE]", "[RAW DATA]", "[SYMBOL] = '" & strSymbol & "'")

While intRef < -51
strSQL = "INSERT INTO INTERMEDIATE (SYMBOL, DATE, HIGH, " & _
"LOW, LAST, VOLUME) SELECT [RAW DATA].SYMBOL, " & _
"[RAW DATA].DATE, [RAW DATA].HIGH, [RAW DATA].LOW, " & _
"[RAW DATA].LAST, [RAW DATA].VOLUME FROM [RAW DATA] " & _
"WHERE (([RAW DATA].SYMBOL = '" & strSymbol & "')" & _
"AND ([RAW DATA].DATE = #" & dteLineDate & "#))"

DoCmd.RunSQL strSQL

intRef = intRef + 1

NextDate:
dteLineDate = dteLineDate + 1
strSQL = "SELECT * FROM [RAW DATA] WHERE " & _
"(([DATE] =#" & dteLineDate & "#) AND ([SYMBOL] ='" & strSymbol &
"'))"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
If DCount("*", "rst") = 0 Then
Set rst = Nothing
Set dbs = Nothing
GoTo NextDate
Else
Set rst = Nothing
Set dbs = Nothing
End If

Wend

Exit Sub

Err_CopyData:
MsgBox Err.Description
Exit Sub

End Sub

 

[Douglas J. Steele]

Both Date and Last are reserved words, and shouldn't be used for your own
objects.

If you cannot (or will not) change the field names, at least put square
brackets around them:

strSQL = "INSERT INTO INTERMEDIATE (SYMBOL, [DATE], HIGH, " & _
"LOW, [LAST], VOLUME) SELECT [RAW DATA].SYMBOL, " & _
"[RAW DATA].[DATE], [RAW DATA].HIGH, [RAW DATA].LOW, " & _
"[RAW DATA].[LAST], [RAW DATA].VOLUME FROM [RAW DATA] " & _
"WHERE (([RAW DATA].SYMBOL = '" & strSymbol & "')" & _
"AND ([RAW DATA].[DATE] = #" & dteLineDate & "#))"

For more information about reserved words, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

 

[Tony Toews]

DoCmd.RunSQL strSQL

I prefer, if DAO, to use Currentdb.Execute strSQL,dbfailonerror
command instead of docmd.runsql. For ADO use
CurrentProject.Connection.Execute strCommand, lngRecordsAffected,
adCmdText

Using this will then suppress the standard "Query has run" message.
Or whatever it is.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tony Toews]

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
If DCount("*", "rst") = 0 Then

Right here you should add
rst.close

Set rst = Nothing

The following statement is not required and might cause you some
troubles.

Set dbs = Nothing
GoTo NextDate

Actually I think I'd redo that code anyhow.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tony Toews]

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

Replace the following code with

If DCount("*", "rst") = 0 Then
Set rst = Nothing
Set dbs = Nothing
GoTo NextDate
Else
Set rst = Nothing
Set dbs = Nothing
End If

dim rstEOF as boolean

rstEOF = rst.eof
rst.close
set rst=nothing
if rstEOF then _
goto NextDate

The above is just aircode so it might not compile.

Note that Dcount, DMin and such are not very efficient and should be
avoided inside loops.

Tony


--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm