SQL question on referencing field with index

[Steve Thomas]

I have a table with the following fields: Name, text1, text2, text3.

In the following code example (Which does not work) i am trying to figure out a way to reference a recordset field using an integer. I can not reference rc!texti. Is there a way to use the value of i so that rc!text + the i will be allowed in code?????? The reason I need to do this is in the live data I have Text1 thru Text34.

*****************************
Dim db as database
Dim rc as recordset
Dim strSQL as string
Dim i as integer

for i = 1 to 3

strSQL = "Select * from Table where Text" & i & " not null;"
Set DB...
Set RC...

rc!texti = "Hi There"

next i

[Frans C]

rc("text" & CStr(i)) = "Hi There"

[Steve Thomas]

Thanks so much. Works Great.

[BruceG]

In principle, your SQL statement should work as is. The problem I see is in the statement:
rc!Texti = "Hi there"
The problem is that you can't use an expression when you reference a field with the "bang" (!) operator. Try this instead:
rc.Fields("Text" & i)

[BruceG]

Hey! Frans beat me to it. He took advantage of the fact that ".Fields" is optional.