Syntax error on "Select . . . Where" statement in VB6, Access 2000, DAO environment
Posted on 2006-07-14
I'm attempting to create two recordsets for use in processing my table with the following code:
Set dbHist = OpenDatabase("c:\Documents and Settings\Joe\Data Files\MS-Visual Basic\SMA\HistoricalData.mdb")
Set rsPctChg = dbHist.OpenRecordset("SELECT TOP 25 *
FROM tblHistoricalData WHERE DateFld = #" & dteDateVar & "# ORDER BY PercentChange Desc", 2)
Set rs = dbHist.OpenRecordset("SELECT *
WHERE Symbol = rsPctChg.Fields("Symbol") AND DateFld >= #" & rsPctChg.Fields("DateFld") & "#
ORDER BY DateFld, Symbol ", 2)
However, at runtime I get a syntax error on the "Set rs =" statement when I say WHERE Symbol = rsPctChg.Fields("Symbol"). If I remove the reference to Symbol, the SQL statement works. However, I want to filter out all symbols that aren't included in my rsPctChg recordset. Symbol is a field in my table. The rsPctChg recordset is set just prior to setting the rs recordset.
What am I doing wrong?