Multiple Search Criteria

[TH]

I am (still :) working on a recipe database. Now I am trying to figure out
how to set it up for an ingredient search. What I want it to be able to do
is three things:
1. Search based on just one ingredient
2 Search based on two ingredients so that the recipe would have to have
both ingredients to show up in the results
3. Search based on three ingredients so that the recipe would have to
have all three ingredients to show up in the results.
Doing just one ingredient is easy. It's when I get to two and three that I
have a problem. The thing is there won't always be a second and third
ingredient to search on. It will depend on how the user wants to search.

I have the database set up with multiple tables. A couple of these include
one for all the details related to the recipe and one for the ingredients
specific to the recipe. These two tables have a one to many relationship
and the join type is to include records where the joined fields from both
tables are equal. I was thinking of using queries but I can't figure out
the logistics of it so it will perform the three things above. I can get it
to where it shows recipes that have say apples or strawberries. When I put
these two on the same line to create an And criteria I get no results even
though there are recipes fitting those criteria. Is it looking for both of
these to be in the same record, same field? How do I get it to look through
all of the ingredients for a particular recipe so it does what #2 and #3
above indicate?

Thank you for your time to anyone who is able to answer. I just have no
idea how to do this.

Tonye

[Paul]

Create a form "IngredientCRForm" with 3 combo boxes "Combo1", "Combo2",
"Combo3" each listing all the ingredients. The source for each combo will
be the same...the ingredients. You can use whatever form and field name you
want.

Then in a query list the ingredients a field and on 3 SEPARATE CRITERIA
LINES enter:
Like "[forms]![IngredientCRForm]![Combo1]" & "*"
Like "[forms]![IngredientCRForm]![Combo2]" & "*"
Like "[forms]![IngredientCRForm]![Combo3]" & "*"

These are OR statements and therefore if the user does not choose any
ingredient, the system will retieve all recipes. Otherwise it will just
recall 1, 2 or 3 ingredients listed. You can uses as many ingrediens as you
want.

Hope this helps
Paul

"TH" <ni*******@bluefrog.com> wrote in message
news:bl*********@enews4.newsguy.com...

I am (still :) working on a recipe database. Now I am trying to figure out
how to set it up for an ingredient search. What I want it to be able to
do
is three things:
1. Search based on just one ingredient
2 Search based on two ingredients so that the recipe would have to
have
both ingredients to show up in the results
3. Search based on three ingredients so that the recipe would have to
have all three ingredients to show up in the results.
Doing just one ingredient is easy. It's when I get to two and three that
I
have a problem. The thing is there won't always be a second and third
ingredient to search on. It will depend on how the user wants to search.

I have the database set up with multiple tables. A couple of these
include
one for all the details related to the recipe and one for the ingredients
specific to the recipe. These two tables have a one to many relationship
and the join type is to include records where the joined fields from both
tables are equal. I was thinking of using queries but I can't figure out
the logistics of it so it will perform the three things above. I can get
it
to where it shows recipes that have say apples or strawberries. When I
put
these two on the same line to create an And criteria I get no results even
though there are recipes fitting those criteria. Is it looking for both of
these to be in the same record, same field? How do I get it to look
through
all of the ingredients for a particular recipe so it does what #2 and #3
above indicate?

Thank you for your time to anyone who is able to answer. I just have no
idea how to do this.

Tonye