ADO recordset handling

[hirenJ]

I know its something quite obvious - but I cant find it!!!
Everytime I give the query some input the recordset is returned empty -- yet when i put the same input into an access query the results come up fine... someone help please!!

Hj
_________________________________________________________
Public Function Get_Contacts2(strRespCode As String)
'GET number of contacts with selected respCode

Dim rst As New ADODB.Recordset
Dim cmd As ADODB.Command
Dim conn As ADODB.Connection
Dim strSQL As String
Dim quo As String

'database connection
Set conn = CurrentProject.Connection

With rst
.ActiveConnection = conn
.Source = "Contact_Table"
.CursorType = adOpenDynamic
.LockType = adLockReadOnly
End With


'generate SQL string
quo = Chr$(34)
strRespCode = quo & "*" & strRespCode & "*" & quo

strSQL = "SELECT Contact_Table.RESPCODE" & _
" FROM Contact_Table" & _
" GROUP BY Contact_Table.RESPCODE" & _
" HAVING (((Contact_Table.RESPCODE) Like " & strRespCode & &quot);"

Debug.Print strSQL

'SQL query
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = conn
.CommandText = strSQL
.CommandType = adCmdText
End With

'execute the query
Set rst = cmd.Execute

'get recordcount

rst.MoveFirst
rst.MoveLast
MsgBox rst.RecordCount

Set rst = Nothing
Set conn = Nothing
Set cmd = Nothing

End Function
__________________________________________________________

 

[braindead2]

I think its how you are building your string. The syntax can be tricky

you might try single quotes

strRespCode = quo & '*' & strRespCode & '*' & quo

then if that don't do it

try using just the string
strRespCode = strRespCode
....
" HAVING (((Contact_Table.RESPCODE) Like '*' '&" & strRespCode & "' & '*'));"

good luck