SQL string

[hirenJ]

Im executing the following string through an ADO command object. However, the resultant recordset is always NULL!

Even stranger -- when I paste this string into the access query designer (in the SQL text screen) the query runs fine...

whats wrong??!!!

______________________________________________________
SELECT Contact_Table.RESPCODE FROM Contact_Table GROUP BY Contact_Table.RESPCODE HAVING (((Contact_Table.RESPCODE) Like "*002*&quot);
______________________________________________________

 

[tomschafer]

Can you post the VBA code?

 

[hirenJ]

sorry for those of you who are already bored with my problems --- P Here is the VBA code behind it...

I even tried running the query using the ADO parameter object - that passes a parameter to an access query --- still didnt work -- whats wrong with it!!?

thankyou everybody...

)

__________________________________________
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 strSQL As String
Dim quo As String

'database connection
Set conn = CurrentProject.Connection

'generate SQL string
quo = Chr$(34)
strSQL = "SELECT Contact_Table.RESPCODE" & _
" FROM Contact_Table" & _
" GROUP BY Contact_Table.RESPCODE" & _
" HAVING (((Contact_Table.RESPCODE) Like " & quo & "*" & strRespCode & "*" & quo & &quot);"

Debug.Print strSQL

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

'execute the query
Set rst = cmd.Execute



'get recordcount
If (rst.EOF = True And rst.BOF = True) Then
MsgBox ("Houston we have a problem&quot
Else
End If
Beep



Set rst = Nothing
Set conn = Nothing
Set cmd = Nothing
End Function
__________________________________________

 

[JoeMiller]

Try this, it's because you're inputting a double-quote with Chr$(34). You need to use single's instead:

strSQL = "SELECT Contact_Table.RESPCODE" & _
" FROM Contact_Table" & _
" GROUP BY Contact_Table.RESPCODE" & _
" HAVING (((Contact_Table.RESPCODE) Like '*" & strRespCode & "*'));" Joe Miller
joe.miller@flotech.net