Type mismatch

S

Suncobra9

When I tried to run this code, the message "Type mismatch" came up. I have
been unable to figure out to problem. If anyone has any ideas, I'd appretiate
it.

Option Compare Database
Option Explicit

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

Private Sub CopyData()
On Error GoTo Err_CopyData

strSymbol = InputBox("Enter Symbol Code:")
intRef = -63
'Starting value for dteLineDate 1 less than first Raw Data date:
strSQL = "SELECT TOP 1 [RAW DATA].[DATE] AS TopDate FROM [RAW DATA] " & _
"WHERE [RAW DATA].SYMBOL = '" & strSymbol & "'"
Set rst = CurrentDb.OpenRecordset(strSQL)
dteLineDate = rst!TopDate - 1

'Now a loop copies 64 records to Intermediate with ascending
'date and incremented REF:

While intRef < 1
strSQL = "INSERT INTO INTERMEDIATE (SYMBOL, [DATE], HIGH, " & _
"LOW, [LAST], VOLUME) SELECT TOP 1 SYMBOL, [DATE], HIGH, " & _
"LOW, [LAST], VOLUME FROM [RAW DATA] WHERE " & _
"[RAW DATA].SYMBOL = '" & strSymbol & "' AND " & _
"[RAW DATA].[DATE] > #" & dteLineDate & "# " & _
"ORDER BY [RAW DATA].[DATE];"
DoCmd.RunSQL strSQL

strSQL = "SELECT TOP 1 INTERMEDIATE.[DATE] AS ThisDate FROM INTERMEDIATE "
& _
"WHERE (INTERMEDIATE.SYMBOL = '" & strSymbol & "' AND " & _
"INTERMEDIATE.REF IS NULL) ORDER BY INTERMEDIATE.[DATE];"
Set rst = CurrentDb.OpenRecordset(strSQL)
dteLineDate = rst!ThisDate
strSQL = "UPDATE INTERMEDIATE SET REF = " & intRef & _
" WHERE (INTERMEDIATE.SYMBOL = '" & strSymbol & "') AND " & _
"(INTERMEDIATE.[DATE] = #" & dteLineDate & "#);"
DoCmd.RunSQL strSQL

intRef = intRef + 1
Wend

Exit Sub

Err_CopyData:
MsgBox Err.Description
Exit Sub

End Sub
 
B

Brendan Reynolds

Does the error disappear if you change the declaration of 'rst' to 'Dim rst
As DAO.Recordset'?

If not, can you identify the line of code that raises the error?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

No current record. 2
Failure to Run 4
Code will not run 1
Code won't run 1
error 13 type mismatch 2
VBA works in orignal but fails in copy of database 1
Loop Help 4
Type Mismatch in Recordset sql 1

Top