Problems with ADO and Dynamic Query - Please help

[Linda]

Hi,

I've been experiencing some problems executing a query in access using ADO.
Here is the static part of the query:

' sPharmSrchSql = "SELECT t_Pharm.[Pharmacist_Name],
t_Pharm.[Pharmacy_Location] , "
'sPharmSrchSql = sPharmSrchSql & "t_Pharm.[Pharmacy_Phone],
t_Pharm.[Sales_Area] , "
'sPharmSrchSql = sPharmSrchSql & "t_Pharm.[Shop_Windows],
t_Pharm.[EXP_TURNO_DIRECT_DELIVERY] , "
'sPharmSrchSql = sPharmSrchSql & "t_Pharm.[UMSATZ_VJ] ,
t_Pharm.[Cust_Main_Id] " & vbCrLf
'sPharmSrchSql = sPharmSrchSql & "FROM t_LocatorIJ, t_SiteVisit,
t_Pharm, Assign_Keys " & vbCrLf
'sPharmSrchSql = sPharmSrchSql & "WHERE "
'sPharmSrchSql = sPharmSrchSql & "t_LocatorIJ.[Cust_Main_Id] =
t_SiteVisit.[Cust_Main_Id] " & vbCrLf
'sPharmSrchSql = sPharmSrchSql & "AND t_SiteVisit.[Cust_Main_Id] =
t_Pharm.[CUST_MAIN_ID] " & vbCrLf
'sPharmSrchSql = AND t_Pharm.[CUST_MAIN_ID] = Assign_Keys.[CUST_MAIN_ID]
" & vbCrLf

The static part returns all the records as you would expect.

My problem is that whenever I add some criteria to reduce the list(depending
on what the user enters), e.g., t_Pharm.[Pharmacist_Name] Like 'dr.*', I get
an empty resultset. I know that there are records that match the criteria.

Another thing I did was I copied and pasted the query into acess (include
the criteria I previously mentioned). The query run ok, which means that the
problem is probably with ADO.

I open my query withthe followign command: rstSrch.Open sPharmSrchSql, con,
adOpenForwardOnly, adLockOptimistic


Any help would be very much appreciated.


Linda

 

[Dan Artuso]

Hi,
The wildcard character in ADO is %, not *