recordset loop

[Sleekmac]

OK I think this should be working, and I can't figure out why it isn't, maybe someone can see what I am missing here please?
What I am trying to do is isolate chunks of a table, and use each chunk as a recordset and do some rs.movelast, next, etc manipulations. I have a string array variable set and that seems to be the problem.

Option Compare Database
Option Base 1
Sub isolatebatch()
Dim ccy(89) As String
Dim x As Integer
Dim conn As ADODB.Connection
Dim rs As New ADODB.Recordset


ccy(1) = "ccy1"
ccy(2) = "ccy2"
'....

Set conn = CurrentProject.Connection
rs.ActiveConnection = conn
For x = 1 To 89
rs.Open "SELECT * FROM [Interp rates] WHERE [F1]= ccy(x) ORDER BY [F4]"
'[operations]
Next x
End Sub



It keeps saying that ccy(x) is an undefined function. This is not a function at all, but a variable, and I have clearly defined it as such above.
Any help would be appreciated!

 

[ejstefl]

rs.Open "SELECT * FROM [Interp rates] WHERE [F1]= " & ccy(x) & " ORDER BY [F4]"

 

[Sleekmac]

ejstefl - Thanks alot for that, I think you have the right idea. It's still not working though, now it says "No value given for one or more required parameters."
When I debug, and hold the mouse over mySQL, it does show the string as ...WHERE [F1]=ATS ORDER [F4]. ATS is the value of ccy(1), so what you suggested is kind of working.

For x = 1 To 89
mySQL = "SELECT * FROM [Interp rates] WHERE [F1]= " & ccy(x) & " ORDER BY [F4]"
rs.Open mySQL
'...
next x

 

[pbaldy]

Because your value is text, try:

mySQL = "SELECT * FROM [Interp rates] WHERE [F1]= '" & ccy(x) & "' ORDER BY [F4]"

 

[ejstefl]

Sorry, missed the fact that it was text. Paul's got it!

 

[Sleekmac]

Thanks alot you two!