Like operatore problem

[Fishing Guy]

I encouter a strange problem with the "Like" operator. I try to search with
like match to a name field:

select * from employe where name like '*joh*'

the query results in 0 records. However, I know the table has multiple
records that the name field has the name "John". and I also vary the sql
statement is valid by create a MS Query (query object) use the same sql. the
MS Query result correct results. but when I embed this sql into my code (I
use ado record set, with this connect string:
Provider=Microsoft.Jet.OLEDB.4.0....), and my recordset return 0 rows.

thanks for any one who can help

 

[John W. Vinson]

I encouter a strange problem with the "Like" operator. I try to search with
like match to a name field:

select * from employe where name like '*joh*'

the query results in 0 records. However, I know the table has multiple
records that the name field has the name "John". and I also vary the sql
statement is valid by create a MS Query (query object) use the same sql. the
MS Query result correct results. but when I embed this sql into my code (I
use ado record set, with this connect string:
Provider=Microsoft.Jet.OLEDB.4.0....), and my recordset return 0 rows.

thanks for any one who can help

For one thing, the word "name" is a reserved word. A Form has a Name property;
a Table has a Name property; a field has a Name property. Access can very
easily get confused as to which name you want! I'd suggest using fields
FirstName and LastName for people's names (so you can sort alphabetically by
last name, for example). If you do want to use reserved words as fieldnames
you must enclose them in [square brackets].

Another possibility - is the field Name a lookup field to some other table? If
so, the table does not actually contain the name, but only a numeric link to
the lookup table.

John W. Vinson [MVP]

 

[John Spencer]

Try
SELECT * FROM Employe WHERE Name like '%joh%'

When using ADO the wild card characters are % and _ instead of * and ?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John W. Vinson]

Try
SELECT * FROM Employe WHERE Name like '%joh%'

When using ADO the wild card characters are % and _ instead of * and ?

<DUH!!!>

Of course you're right - thanks, John, and my apologies for missing that.

John W. Vinson [MVP]

 

[Jamie Collins]

Try
SELECT * FROM Employe WHERE Name like '%joh%'

When using ADO the wild card characters are % and _ instead of * and ?

Alternatively, use ALIKE in either DAO (ANSI-89 Query Mode) and ADO
(ANSI-92 Query Mode) with the wildcard characters % and _ instead of
* and ?

Jamie.

--