Search by keyword in a filter

G

Guest

I have a database of apparel items. My goal is to have a switchboard button
that users can click that will prompt them to enter a keyword or keywords
that they want to look for anywhere in the item name. For example, if they
type "floral", items such as "Floral Tights", and "Knit Floral Top" will
display. The filter I currently have has the criteria "[Enter item name]"
for the criteria, but of course it only dipslays results if the user knows
the complete name of the item. If they are looking for the Knit Floral Top,
and just type "Floral Top" (because they don't know the exact name), they of
course don't the correct result.

The only way I know how to search in any part of the field (as opposed to
the whole field) is by using the Find function while in the form. I'm sure
there's some way to do this in a filter that I could then turn into a macro,
then assign to the switchboard button. I'll bet it's something simple, but
I'm fairly new at Access and can't seem to figure it out. Please let me know
if more information is needed. I appreciate all assistance.
 
A

Allen Browne

Presumably you have a query and the [Enter item name] is in the Criteria
row?

Change it to:
Like "*" & [Enter item name] & "*"

If you would like them to be able to type into a box on your form instead of
the popup box, see:
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html
 
G

Guest

You can use the Like operator with the * wildcard character. There are
various ways you could do it; a simple method would be to base the form or
report in which you want to display the results of the search on a query with
a parameter on the Item column. In query design view you'd enter something
like the following in the 'criteria' row of the Item column:

Like "*" & [Enter all or part of item name:] & "*"

IN SQL view your query would then look something like:

SELECT *
FROM YourTable
WHERE Item Like "*" & [Enter all or part of item name:] & "*"
ORDER BY Item;

If you want to make the propmpt optional so that all records are returned if
the user leaves it blank then you can test for NULL. The criteria entered in
design view would then be:

Like "*" & [Enter all or part of item name:] & "*" OR [Enter all or part of
item name:] IS NULL

If you do the latter you'll find that Access will move things around in
design view after you save the query, but it will work in exactly the same
way.

All you need to do from the button on your switchboard is open any form or
report with the above query AS its RecordSource. The user will then be
prompted to enter the name in whole or in part, and the form or report will
display the results sorted alphabetically by Item (in a report you should
sort it by means of the reports internal sorting and grouping mechanism in
design view in fact, rather than in the query, whose sort order the report
might or might not ignore).

Ken Sheridan
Stafford, England
 
G

Guest

Thank you, that worked perfectly! What I was trying to enter was close, but
I left out the "&" signs. I knew it was something simple. :0)

Allen Browne said:
Presumably you have a query and the [Enter item name] is in the Criteria
row?

Change it to:
Like "*" & [Enter item name] & "*"

If you would like them to be able to type into a box on your form instead of
the popup box, see:
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

LyndsyJo said:
I have a database of apparel items. My goal is to have a switchboard
button
that users can click that will prompt them to enter a keyword or keywords
that they want to look for anywhere in the item name. For example, if
they
type "floral", items such as "Floral Tights", and "Knit Floral Top" will
display. The filter I currently have has the criteria "[Enter item name]"
for the criteria, but of course it only dipslays results if the user knows
the complete name of the item. If they are looking for the Knit Floral
Top,
and just type "Floral Top" (because they don't know the exact name), they
of
course don't the correct result.

The only way I know how to search in any part of the field (as opposed to
the whole field) is by using the Find function while in the form. I'm
sure
there's some way to do this in a filter that I could then turn into a
macro,
then assign to the switchboard button. I'll bet it's something simple,
but
I'm fairly new at Access and can't seem to figure it out. Please let me
know
if more information is needed. I appreciate all assistance.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top