Pete,

Thanks for the reply.  I had to substitute the "%" for the "*" in the 
calling code.

> The Jet engine is preprocessing your query, and it doesn't work 
according to
the same set of syntax rules that Access does.  I think you have to use %
instead of ?.

Pete

-----Original Message-----
From: gbusby@l... [mailto:gbusby@l...]
Sent: Thursday, December 12, 2002 11:21 PM
To: professional vb
Subject: [pro_vb] Trouble with calling Access select query


I have an Access 2000 select query that returns the proper records when I
run it in Access 2000.  However, when I call the query from VB6 using a
command
object it returns 0 records.  Here's the Access 2000 query:

SELECT DISTINCT GenericName
FROM Drugs
WHERE GenericName LIKE ?;


The vbCode that calls it follows:

Private Sub cmdLookup_Click()

Dim recLU As ADODB.Recordset
Dim cmmCommand As ADODB.Command
Dim parParameter As ADODB.Parameter
Dim strDrug As String
Dim lngSize As Long

    Set cmmCommand = New ADODB.Command
    Set parParameter = New ADODB.Parameter
    Set recLU = New ADODB.Recordset

    strDrug = "'*" & SSddeDrug.Text & "*'"
    lngSize = Len(strDrug)

    ' Configure command and parameter object for calling qrydef
    cmmCommand.CommandText = "qrydef_GenericDrugLU"
    cmmCommand.CommandType = adCmdStoredProc
    cmmCommand.ActiveConnection = g_cnnEMRSConnection
    Set parParameter = cmmCommand.CreateParameter("GenericName", adBSTR,
adParamInput _
        , lngSize, strDrug)
    cmmCommand.Parameters.Append parParameter

    recLU.Open cmmCommand, , adOpenStatic, adLockReadOnly, adCmdStoredProc

    Set pvlGenericName.ListSource = recLU
    pvlGenericName.ListMember = recLU.Fields("GenericName").Name

End Sub

Any help/guidance is appreciated.
---
Change your mail options at http://p2p.wrox.com/manager.asp or
to unsubscribe send a blank email to