Search for a title containing two words that are not together

[Guest]

I've created a pretty nifty little search form for our technical library
database. Now my users want to be able to search for a book title (one of
the fields in the db) with two words that may or may not appear together in
the title. I'm using the dialog box style (["Enter words or a phrase in the
title"]) to ask the user for input, but it seems I can't use that with "Like"
when I create a new expression to include the user's input. No matter what I
try (using "Or" or using "Like" or using two queries, etc.) I either get a
syntax error or blank results. Surely this can be done. I just don't know
how. (And, as usual, "Help" doesn't.)

Thanks for your time and expertise.

 

[fredg]

I've created a pretty nifty little search form for our technical library
database. Now my users want to be able to search for a book title (one of
the fields in the db) with two words that may or may not appear together in
the title. I'm using the dialog box style (["Enter words or a phrase in the
title"]) to ask the user for input, but it seems I can't use that with "Like"
when I create a new expression to include the user's input. No matter what I
try (using "Or" or using "Like" or using two queries, etc.) I either get a
syntax error or blank results. Surely this can be done. I just don't know
how. (And, as usual, "Help" doesn't.)

Thanks for your time and expertise.

Try this SQL where clause:

WHERE YourTable.Title Like "*" & [First word] & "*" AND
YourTable.Title Like "*" & [Second word] & "*"

Only records that contain both words will be returned.
Change the field and table names as needed.

 

[Guest]

I've created a pretty nifty little search form for our technical library
database. Now my users want to be able to search for a book title (one of
the fields in the db) with two words that may or may not appear together in
the title. I'm using the dialog box style (["Enter words or a phrase in the
title"]) to ask the user for input, but it seems I can't use that with "Like"
when I create a new expression to include the user's input. No matter what I
try (using "Or" or using "Like" or using two queries, etc.) I either get a
syntax error or blank results. Surely this can be done. I just don't know
how. (And, as usual, "Help" doesn't.)

Thanks for your time and expertise.

Try this SQL where clause:

WHERE YourTable.Title Like "*" & [First word] & "*" AND
YourTable.Title Like "*" & [Second word] & "*"

Only records that contain both words will be returned.
Change the field and table names as needed.

Thanks, Fredg. This is a great start. What's not addressed is that it is
the user who is (prompted by my dialog box) entering the word(s). I won't
know if there are more than two words or only one or exactly two, nor will I
know what those words are until after the query is run.
The question to the user is "Enter words or a phrase in the title", and the
response becomes an expression. Should I change what I'm asking for or ask
for it in a different way?

 

[Guest]

Search on a 'Space' to find multiple word titles. Use this for criteria --
Like "* *"

OR
Add an output filed in the query design grid like this --
Two Word: InStr([YourTitleField]," ")

And use >0 (greater than zero) as criteria.

 

[Guest]

Karl, I'm not sure if this answers my question, but I'll certainly give it a
try. Thanks for your help.

Search on a 'Space' to find multiple word titles. Use this for criteria --
Like "* *"

OR
Add an output filed in the query design grid like this --
Two Word: InStr([YourTitleField]," ")

And use >0 (greater than zero) as criteria.

I've created a pretty nifty little search form for our technical library
database. Now my users want to be able to search for a book title (one of
the fields in the db) with two words that may or may not appear together in
the title. I'm using the dialog box style (["Enter words or a phrase in the
title"]) to ask the user for input, but it seems I can't use that with "Like"
when I create a new expression to include the user's input. No matter what I
try (using "Or" or using "Like" or using two queries, etc.) I either get a
syntax error or blank results. Surely this can be done. I just don't know
how. (And, as usual, "Help" doesn't.)

Thanks for your time and expertise.

 

[John Spencer]

The only way to really handle this would be to use a form and some vba to
split the words out.

The best I can think of with a parameter prompt would be to find the words
in order.

LIKE "*" & Replace([Enter words or a phrase in the title]," ","*") & "*"

If the user input "John Spencer" they would get records with John followed
by Spencer anywhere in the title, but would not get records with Spencer
followed by John in the title.

The above will work in Access versions after Access 2000 and in Access 2000
if it has the latest service packs.

I've created a pretty nifty little search form for our technical
library
database. Now my users want to be able to search for a book title (one
of
the fields in the db) with two words that may or may not appear
together in
the title. I'm using the dialog box style (["Enter words or a phrase
in the
title"]) to ask the user for input, but it seems I can't use that with
"Like"
when I create a new expression to include the user's input. No matter
what I
try (using "Or" or using "Like" or using two queries, etc.) I either
get a
syntax error or blank results. Surely this can be done. I just don't
know
how. (And, as usual, "Help" doesn't.)

Thanks for your time and expertise.

Try this SQL where clause:

WHERE YourTable.Title Like "*" & [First word] & "*" AND
YourTable.Title Like "*" & [Second word] & "*"

Only records that contain both words will be returned.
Change the field and table names as needed.

Thanks, Fredg. This is a great start. What's not addressed is that it is
the user who is (prompted by my dialog box) entering the word(s). I won't
know if there are more than two words or only one or exactly two, nor will
I
know what those words are until after the query is run.
The question to the user is "Enter words or a phrase in the title", and
the
response becomes an expression. Should I change what I'm asking for or
ask
for it in a different way?