Recordset problems

[Krystoff]

Hello all, I am using a recordset to open a table that has nothing but numbers in it. I then take and use that number as search criteria in a comments field of another recordset using Like. It seems to work fine, except for the second recordset containing the search criteria. I can use the same SQL string in a query and it brings up data, but not when I use it as a recordset. Is there anything in my code that would tell you what I am doing wrong?

Code:
****************************************
Dim rst As ADODB.Recordset
Dim rst2 As ADODB.Recordset

Set rst = New ADODB.Recordset
Set rst2 = New ADODB.Recordset

Set rst.ActiveConnection = CurrentProject.Connection
rst.LockType = adLockOptimistic
rst.Open "Table1"
If Not rst.EOF Then
'Move to the first record
rst.MoveFirst
Do Until rst.EOF
rst2.Open "SELECT Table2.Comments FROM Table2 WHERE (((Table2.Comments) Like '*" & rst.Fields(0).Value & "*'))", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If rst2.Fields(0).Value = "" Then
Debug.Print rst.Fields(0).Value
Else
Debug.Print rst2.Fields(0).Value
End If
rst2.Close
rst.MoveNext
Loop
End If

rst.Close
Set rst = Nothing
Set rst2 = Nothing

 

[jdegeorge]

Krystoff

I've found that sql in a query isn't always the same as sql in a recordset.

You have:

"SELECT Table2.Comments FROM Table2 WHERE Table2.Comments Like '*" & rst.Fields(0).Value & "*'"



Jim DeGeorge

 

[Krystoff]

The SQL runs, it just gives me a blank instead of an actual field.

How do I print the actual field itself instead of using rst.fields(0).value

 

[Krystoff]

Ok, I found out the problem. I needed to use % instead of * as my wildcard.

Thanks!

 

[jdegeorge]

Good going! Sorry I wasn't understanding your question.

Jim DeGeorge