Hi all, can anyone shed ANY light on this SQL query?!! Ive been puzzled over this for month -- ive tried generating the Query in 10 different ways, including different syntax, using the ADO.parameter object, command object --- still not working!!
At the moment I have been passing strRespCode to the function. This is of text data type and represents a numeric code i.e. 002 -- the SQL string generates correctly, however, it doesnt seem to execute -- any ideas?
Hj
===========================================================
Public Function Get_Contacts2(strRespCode As String)
'GET number of contacts withselected respCode
Dim rst As New ADODB.Recordset
Dim cmd As ADODB.Command
Dim strSQL As String
Dim quo As String
Dim criteria As String
'database connection
Set conn = CurrentProject.Connection
Set rst = New ADODB.Recordset
'generate SQL string
quo = Chr$(34)
criteria = "*" & strRespCode & "*"
strSQL = "SELECT Contact_Table.RESPCODE
FROM Contact_Table GROUP BY Contact_Table.RESPCODE
HAVING (((Contact_Table.RESPCODE) Like'" & criteria & "'));"
Debug.Print strSQL
rst.Open strSQL, conn, adOpenStatic, adLockReadOnly, adCmdText
'get recordcount
If (rst.EOF = True And rst.BOF = True) Then
MsgBox ("Houston we have a problem"
Else
Do Until rst.EOF
Debug.Print rst![RespCode]
rst.MoveNext
Loop
End If
Stop
Set rst = Nothing
Set conn = Nothing
Set cmd = Nothing
End Function
===========================================================
