Mulitple word search in single field

[Lowell]

I have a recipe database that I need to be able to look up multiple ingredients to find a specific recipe.



I have all the ingredients in one field.

I would like to type: "Flour, Cajun, Fish" in a search box or multiple search boxes or something down that line.

Then the query would bring up all the recipes which contain, at least, those three ingredients.
(e.i. Southern Battered Cajun Fish, Louisianna Cajun Catfish, Southern French Entree,)

I am certian this is possible, but I have run to a dead end on my trial and error.

I have almost no talent in VBA.

If VBA is the only way to do it, I will give it a try.

Please help me if you can.

[orange]

Please tell us more or show an example of this

I have all the ingredients in one field.

[Lowell]

Here is a picture

[rzw0wr]

Are you wanting to do this in a query,a form or the table?

Dale

[Lowell]

I had thought I would type in the different ingredients in a query parameter box and it would bring up the info in the query.

I had thought I would then use a form for the end user and run the form off of the query for an easy to use interface.

What is your suggestion?

As I mentioned earlier, I have run out of ideas on how to do what I want done.

[rzw0wr]

I would use a form and VBA to build an SQL statement.
Parameter box will work if you knew the exact length of the list. Like 5 or 6 ingredients.


Dale

[Lowell]

Well, that sounds like a good idea but I'm sorry, I have no idea where to start on writing VBA on a form.

If you can at least get me started, I can see if I can blunder my way through.

[rzw0wr]

Well Lowell,
The guy you need to talk to is orange.
I may be able to stumble through it and get you something to work.
However Orange will get you there faster and better looking code.

I am not sure where to start myself.

Dale

[Lowell]

Okay thanks for your help,

Orange, would you help me out?

[rzw0wr]

Look this over.
It will make you SQL list.
I am trying to get it to show in a query or on a form.
Put this in a form behind a command button.

Dim intIng As Integer
Dim strSQL As String
Dim strInger As String
Dim strIngerTemp As String

strInger = ""
strSQL = ""

For intIng = 1 To InputBox("End")
    strInger = InputBox("ingredient " & intIng & "; ")
    Select Case intIng
        Case Is = 1
            strIngerTemp = "ingredient = " & strInger
        Case Is > 1
            strIngerTemp = strIngerTemp & " AND ingredient = " & strInger
    End Select
    Debug.Print strIngerTemp
Next intIng
strSQL = "SELECT recipe, ingredient FROM YourTableName WHERE " & strIngerTemp
    Debug.Print strSQL
'Get it into a query or form form here.

Also needs error code.
To see the debug.print, press ctl+G

Dale

[June7]

Look at this tutorial for code that will build filter criteria from a multi-select list box http://allenbrowne.com/ser-50.html

[Lowell]

I tried that and everything I tried didn't work.

The report and query mentioned in allen brownes website doesn;t exist in the three Northwind Sample databases I looked at so its kinda hard to figure out what I should do different with nothing to compare too.

What happens now after following instructions to the best of my ablities is:

I click the preview button and a little box that asks for comments comes up.
I click ok and the report with all the info from the database is populated.

I guess I need some more help

[June7]

If you want to provide db with your attempted code for analysis, follow instructions at bottom of my post.

[orange]

Lowell,

I tried that and everything I tried didn't work.

That statement alone does not help you or the reader.
Please be specific in telling us what you did exactly and what happened.
Show us some code and data or screen shots; tell us the error message etc.
Debugging with no information is impossible.

I recommend you post your db as June7 has advised; and she'll review and comment.

[rzw0wr]

try this,

Dim intIng As Integer
Dim strSQL As String
Dim strInger As String
Dim strIngerTemp As String
 
strInger = ""
strSQL = ""
 
For intIng = 1 To InputBox("End")
    strInger = InputBox("ingredient " & intIng & "; ")
    Select Case intIng
        Case Is = 1
            strIngerTemp = "ingredient = " & strInger
        Case Is > 1
            strIngerTemp = strIngerTemp & " AND ingredient = " & strInger
    End Select
    Debug.Print strIngerTemp
Next intIng
strSQL = "SELECT recipe, ingredient FROM YourTableName WHERE " & strIngerTemp
    ‘Debug.Print strSQL
DoCmd.OpenReport "recipe", acViewPreview, , strSQL, acWindowNormal

Dale