Query returns no data

[Guest]

This is my first database using Access and I'm finding it difficult to use.
I have had no problem construction my table and form with a search button
leading to a query. The database is a list of publications with fields:
Date and Subject. I have constructed my query to direct to this database
with the search field "subject" in ascending order. In the criteria field I
have entered: like [Search By Subject]. When i run the query searching for a
keyword from the subject field I get nothing... unless I surround the keyword
with asterisks IE: *KEYWORD*. I'm not sure if "like" is the argument or
operator is should be using. Any help would be greatly appreciated!

Larry B.

 

[gls858]

This is my first database using Access and I'm finding it difficult to use.
I have had no problem construction my table and form with a search button
leading to a query. The database is a list of publications with fields:
Date and Subject. I have constructed my query to direct to this database
with the search field "subject" in ascending order. In the criteria field I
have entered: like [Search By Subject]. When i run the query searching for a
keyword from the subject field I get nothing... unless I surround the keyword
with asterisks IE: *KEYWORD*. I'm not sure if "like" is the argument or
operator is should be using. Any help would be greatly appreciated!

Larry B.

Straight from the Access help file:

Like Operator
Compares a string expression to a pattern in an SQL expression.

Syntax
expression Like “patternâ€

The Like operator syntax has these parts:

Part Description
expression SQL expression used in a WHERE clause .
pattern String or character string literal against which expression is
compared.


Remarks
You can use the Like operator to find values in a field that match the
pattern you specify. For pattern, you can specify the complete value (for
example, Like “Smithâ€), or you can use wildcard characters to find a range
of values (for example, Like “Sm*â€).

In an expression, you can use the Like operator to compare a field value to
a string expression. For example, if you enter Like “C*†in an SQL query,
the query returns all field values beginning with the letter C. In a
parameter query , you can prompt the user for a pattern to search for.

The following example returns data that begins with the letter P followed
by any letter between A and F and three digits:

Like “P[A-F]###â€

The following table shows how you can use Like to test expressions for
different patterns.


Kind of match
Pattern Match
(returns True) No match
(returns False)
Multiple characters a*a aa, aBa, aBBBa aBC
*ab* abc, AABB, Xab aZb, bac
Special character a[*]a a*a aaa
Multiple characters ab* abcdefg, abc cab, aab
Single character a?a aaa, a3a, aBa aBBBa
Single digit a#a a0a, a1a, a2a aaa, a10a
Range of characters [a-z] f, p, j 2, &
Outside a range [!a-z] 9, &, % b, a
Not a digit [!0-9] A, a, &, ~ 0, 1, 9
Combined a[!b-m]# An9, az0, a99 abc, aj0


See Also
SQL Expressions Using Wildcard Characters in String Comparisons
WHERE Clause

 

[John Vinson]

This is my first database using Access and I'm finding it difficult to use.
I have had no problem construction my table and form with a search button
leading to a query. The database is a list of publications with fields:
Date and Subject. I have constructed my query to direct to this database
with the search field "subject" in ascending order. In the criteria field I
have entered: like [Search By Subject]. When i run the query searching for a
keyword from the subject field I get nothing... unless I surround the keyword
with asterisks IE: *KEYWORD*. I'm not sure if "like" is the argument or
operator is should be using. Any help would be greatly appreciated!

Larry B.

Try a criterion of

LIKE "*" & [Search by subject] & "*"

This will put in the wildcards for you.

If there are no wildcards in the criterion (such as * for "match any
string", ? for "match any single character", or many others) the LIKE
operator returns only exact matches, the same as the = operator.

John W. Vinson[MVP]