Select statement with weird behaviour

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Einy - 09 May 2005 13:48 GMT

Hi all,

I have the following select statement for a search form in Access:

SELECT ToPrint.[box #], ToPrint.[MATH AREA], ToPrint.topics,
ToPrint.Activity, ToPrint.[More info], ToPrint.Source
FROM ToPrint
WHERE (((ToPrint.[MATH AREA]) Like ([Forms]![Search]![Keyword]))) OR
(((ToPrint.topics) Like ([Forms]![Search]![Keyword]))) OR
(((ToPrint.Activity) Like ([Forms]![Search]![Keyword]))) OR (((ToPrint.[More
info]) Like ([Forms]![Search]![Keyword]))) OR (((ToPrint.Source) Like
([Forms]![Search]![Keyword])));

When running the search, the query returns results only if the keyword is in
the first field and ignore all other fields... for example, Math Area has
"Geometry" so if the Key word is Geometry, the query returns values, if the
keyword is something in other fields, the query is blank. Also, if the
keyword is "Geo", the query is also blank... I tried using the % truncation
or wild card, but same results.

Can anyone tell me what is wrong in my SQL statement??

P.S. All fields used in the statement are indexed.

Thank you.

Einy

Reply to this Message

Dirk Goldgar - 09 May 2005 14:55 GMT

> Hi all,
>
[quoted text clipped - 24 lines]
>
> Einy

You need to use wild-card characters to match those portions of the
field that don't contain the keyword. You say you've tried it, but have
you tried it like this?

SELECT
ToPrint.[box #],
ToPrint.[MATH AREA],
ToPrint.topics,
ToPrint.Activity,
ToPrint.[More info],
ToPrint.Source
FROM ToPrint
WHERE
(ToPrint.[MATH AREA]
Like '*' & [Forms]![Search]![Keyword] & '*')
OR
(ToPrint.topics
Like '*' & [Forms]![Search]![Keyword] & '*')
OR
(ToPrint.Activity
Like '*' & [Forms]![Search]![Keyword] & '*')
OR
(ToPrint.[More info]
Like '*' & [Forms]![Search]![Keyword] & '*')
OR
(ToPrint.Source
Like '*' & [Forms]![Search]![Keyword] & '*');

Note: I used the "*" wild-card character based on the assumption that
this is in an MDB and not in a pass-through query to an ODBC database.
If it's in an ADP, or a pass-through query to SQL Server, you'd use the
"%" character. If it's some other DBC database, you might have to use a
different character.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

Van T. Dinh - 09 May 2005 15:05 GMT

You should use Like with wildcards, e.g. * and ?. Without wildcards, Like
will operate just like the equal comparison operator (on Text values).

Check Access Help on wildcards and the Like operator.

Signature

HTH
Van T. Dinh
MVP (Access)

> Hi all,
>
[quoted text clipped - 23 lines]
>
> Einy

Reply to this Message