Why doesn't Like work??

[Ron H.]

I am trying to do a simple query to find records that contain keywords

SELECT tblKeywords.KeyWord, tblKeywords.Code
FROM tblKeywords
WHERE (((tblKeywords.KeyWord) Like '%HOSE%'));

I know that there are several records with the word HOSE in them but the
query always returns nothing.

Example:

KeyWord Code
HOSE CLAMP 1234
HOSE VACUUM 3245
AIR HOSE 5546

I would expect all of these records to be returned with query shown???
Ron H.

[Saintor]

Shouldn't it be
Like '*HOSE*' ?
"Ron H." <ro************@mmm.com> wrote in message
news:c4**********@tuvok3.mmm.com...

I am trying to do a simple query to find records that contain keywords

SELECT tblKeywords.KeyWord, tblKeywords.Code
FROM tblKeywords
WHERE (((tblKeywords.KeyWord) Like '%HOSE%'));

I know that there are several records with the word HOSE in them but the
query always returns nothing.

Example:

KeyWord Code
HOSE CLAMP 1234
HOSE VACUUM 3245
AIR HOSE 5546

I would expect all of these records to be returned with query shown???
Ron H.

[Peter Hoyle]

'*' is the wild card character in Access.

So your expression would need to be Where((tblKeywords.KeyWord) Like
'*HOSE*');

Cheers,
Peter

[Ron H]

Thanks! I guess that was supposed to be intuitive? I was using the MS
SQLserver online SQL help and I guess Access is a subset that doesn't follow
the same standard.... what standard....
Ron H.

[Larry Linson]

Access is not a "subset" of SQL Server. In fact, strictly speaking, _Access_
is the user interface and database developer tool, not actually a database:
the default database installed with and used by Access is Microsoft Jet
(formerly JET for Joint Engine Technology, but that was changed a few
releases ago). Access can also be used as a client application with a number
of server databases.

Jet has its own "dialect" of the SQL language, as does SQL Server, as does
Informix, as does Oracle... it's just that some of the server databases are
closer to the SQL approved by the standards bodies. But, you should expect
to run into difficulties if you use the help from any particular database
expecting it to be the authority for some other database.

I'm currently doing some work for a client company, on an Access ADP (aka
"project") with data in MS SQL Server. The query you show would have been
appropriate in that environment, because you use the SQL Server syntax ("%"
instead of "*"), rather than Access/Jet syntax.

Larry Linson
Microsoft Access MVP

"Ron H" <12****@attbi.com> wrote in message
news:dGjcc.195988$po.1013479@attbi_s52...

Thanks! I guess that was supposed to be intuitive? I was using the MS
SQLserver online SQL help and I guess Access is a subset that doesn't follow the same standard.... what standard....
Ron H.