using LIKE operator in SQL

  • Thread starter Thread starter Brian Flewwelling
  • Start date Start date
B

Brian Flewwelling

I have a query with Like [FirstLetter] & "*" which works fine when i run it,
but when i put the corresponding SQL code into VBA it returns a Type
Mismatch error.

the query's name is 'qFindME', with the following SQL: it asks for the
parameter value and performs properly)
PARAMETERS Letter Text ( 255 );
SELECT DISTINCT EqRunningLines.HorseName
FROM EqRunningLines
WHERE (((EqRunningLines.HorseName) Like [Letter] & "*"));


While the code:

Set qry = CurrentDb.QueryDefs("qFindMe")
qry.Parameters("[Letter]") = sFirstLetter
Set rst = qry.OpenRecordset

returns Type Mismatch error on the last line (proper dims exist for qry)


And the code:
sSQL = "SELECT EqRunningLines.HorseName FROM EqRunningLines " & _
"WHERE (((EqRunningLines.HorseName) Like " & Chr$(34) & sFirstLetter &
"*" & Chr$(34) & "));"
Set rst = CurrentDb.OpenRecordset(sSQL)

also returns Type Mismatch error on the last line


Help, please

Brian
 
Since you don't say, I have to guess that you're using Access 2000 or newer.
Further, I'm assuming that your declaration for rst is:

Dim rst As Recordset

Change that to

Dim rst As DAO.Recordset

Access 2000 or 2002 don't include a reference to DAO by default (instead,
all they have is a reference to ADO). While Access 2003 does include a
reference to DAO, it's lower in precedence than ADO.

There's a Recordset object in both the ADO and DAO models. When you have
both references, you'll find that you'll need to "disambiguate" certain
declarations, because objects with the same names exist in the 2 models. For
example, to ensure that you get a DAO recordset, you'll need to use Dim
rsCurr as DAO.Recordset (to guarantee an ADO recordset, you'd use Dim rsCurr
As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset

The other alternative is to remove the reference to ADO ("Microsoft ActiveX
Data Objects 2.x Library") if you're not using ADO.
 
Thank you Douglas!



Douglas J. Steele said:
Since you don't say, I have to guess that you're using Access 2000 or newer.
Further, I'm assuming that your declaration for rst is:

Dim rst As Recordset

Change that to

Dim rst As DAO.Recordset

Access 2000 or 2002 don't include a reference to DAO by default (instead,
all they have is a reference to ADO). While Access 2003 does include a
reference to DAO, it's lower in precedence than ADO.

There's a Recordset object in both the ADO and DAO models. When you have
both references, you'll find that you'll need to "disambiguate" certain
declarations, because objects with the same names exist in the 2 models. For
example, to ensure that you get a DAO recordset, you'll need to use Dim
rsCurr as DAO.Recordset (to guarantee an ADO recordset, you'd use Dim rsCurr
As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset

The other alternative is to remove the reference to ADO ("Microsoft ActiveX
Data Objects 2.x Library") if you're not using ADO.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Brian Flewwelling said:
I have a query with Like [FirstLetter] & "*" which works fine when i run it,
but when i put the corresponding SQL code into VBA it returns a Type
Mismatch error.

the query's name is 'qFindME', with the following SQL: it asks for the
parameter value and performs properly)
PARAMETERS Letter Text ( 255 );
SELECT DISTINCT EqRunningLines.HorseName
FROM EqRunningLines
WHERE (((EqRunningLines.HorseName) Like [Letter] & "*"));


While the code:

Set qry = CurrentDb.QueryDefs("qFindMe")
qry.Parameters("[Letter]") = sFirstLetter
Set rst = qry.OpenRecordset

returns Type Mismatch error on the last line (proper dims exist for qry)


And the code:
sSQL = "SELECT EqRunningLines.HorseName FROM EqRunningLines " & _
"WHERE (((EqRunningLines.HorseName) Like " & Chr$(34) & sFirstLetter &
"*" & Chr$(34) & "));"
Set rst = CurrentDb.OpenRecordset(sSQL)

also returns Type Mismatch error on the last line


Help, please

Brian
Click to expand...
Click to expand...
 
* is the wild card symbol for DAO, which is what the original poster was
trying to use. You only use % with ADO (or with other DBMS)
 
Back
Top