Type mismatch


G

Guest

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
 
Ad

Advertisements

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
Code won't run 1
Code will not run 1
Failure to Run 4
error 13 type mismatch 2
Details section is scrolling when insert new record 2
error 3464 3
Invalid Use of Null 3

Top