Select statement with weird behaviour

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Einy said:
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
Click to expand...

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.
 
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.
 
Back
Top