Code will not run

[Guest]

This code will not run. I've tried every way I can think of to fix it, but
nothing's worked. If anyone has any ideas, I'd appretiate it.

Option Compare Database
Option Explicit

Dim strSymbol As String
Dim dteLineDate As Date
Dim intRef As Integer
Dim strSQL As String

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
If IsNull (DLookup ("[DATE]","[RAW DATA]", _
"([DATE] = dteLineDate) AND ([SYMBOL] = strSYMBOL)"))
Then GoTo NextDate
Else: End If

Wend

Exit Sub

Err_CopyData:
MsgBox Err.Description
Exit Sub

End Sub

 

[Guest]

What do you mean by "will not run"? Can you be a bit more specific? Have
you tried putting a break point in the code and single-stepping through it
with the F8 key? Do you get any errors? Does your code compile okay?

Is dteLineDate being initialized as expected? Part of the problem might be
on the first and second lines of your strSQL statement. Notice how these two
lines do not include the extra space at the end, similar to lines 3-5 (and
the last line, line 6, would not require the extra space). Normally, I would
expect Access to be forgiving enough of this and not throw an error. Try
inserting a debug.print strSQL to see if a valid SQL statement is being
produced. Copy the SQL statement from the Immediate Window (Ctrl G) and paste
it into the SQL View of a new query. It should run without any errors.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________