rs.open sqlcode in ADO recordset not working

[Tim]

I have been looking thru the questions and examples here and have found a
work-around for my problem using DAO recordsets (ie set
rs=db.openrecordset(sql)), but I would like to know the correct way to use
rs.open in ADO with a select query. The problem I have is that the
rsMatch.open sqlMatch line in my code below does not put the query results
into my rsMatch variable. So, the rsMatch recordset is empty. I have verified
this by debug.print commands and also by stepping thru the program and
looking at the values in the watch window. Here is my code below, please tell
me where I am going wrong. import and tblMember are tables in my database.

Sub import2()
Dim myConnection As ADODB.Connection
Set myConnection = CurrentProject.Connection
Dim rsImport As New ADODB.Recordset
rsImport.ActiveConnection = myConnection
rsImport.Open "import", , adOpenDynamic, adLockOptimistic
Dim rsMatch As New ADODB.Recordset
rsMatch.ActiveConnection = myConnection
Dim fldMN, fldHUMID, fldDoB, fldMarket As String
fldMN = rsImport.Fields("MemberName")
fldHUMID = rsImport.Fields("MemberUMID")
fldDoB = rsImport.Fields("DateofBirth")
fldMarket = rsImport.Fields("Market")

sqlMatch = "SELECT tblMember.MemberName, tblMember.HUMID, tblMember.DoB,
tblMember.Market FROM tblMember WHERE (((MemberName) Like '*" & fldMN & "*')
AND ((HUMID) Like '*" & fldHUMID & "*') AND ((DoB)=#" & fldDoB & "#) AND
((Market) Like '*" & fldMarket & "*'))"
rsMatch.Open sqlMatch
If Not rsMatch.EOF Or Not rsMatch.BOF Then
rsMatch.MoveFirst
Debug.Print rsMatch.Fields(0) & " " & rsMatch.Fields(1) & " " &
rsMatch.Fields(2) & " " & rsMatch.Fields(3)
Else
Debug.Print Time & " Nothing"
End If
rsMatch.Close
Set rsMatch = Nothing
End Sub

 

[kingston via AccessMonster.com]

Replace "*" with "%" as the wildcard in your SQL string.