form based parameter query with multiple values

[Dave_epic]

Hi this is a two part problem really.

I have made a form based parameter query which uses a wild card search.
All data in the 'table' is in text format. At the moment the form has 3 parameters keyword1, keyword2 and keyword3. The sql for this is below.


SELECT column1,
FROM table
WHERE [column1] Like "*" & [Forms]![name_of_form]![keyword1] & "*"
AND [column1] Like "*" & [Forms]![name_of_form]![keyword2] & "*"
AND [column1] Like "*" & [Forms]![name_of_form]![keyword3] & "*";


As you can see the parameter query works fine with 'AND' so I can enter 1 keyword and get all the records that contain that keyword or I can enter 2 or 3 keywords and get all the records that must contain those 2 or 3 keywords.

What I also want is to have 3 more keywords on the form where the results will have records with either keyword 1 or keyword 2 or keyword 3.
I have tried using 'OR' in a new query based on the one above but it just returns all records.

part 2 of question is how I can adapt the query above to search in 2 or more columns rather than just the one as it is at the moment.

Thanks

 

[fkegley]

I think you're going to need to develop the SQL statement, if you are not already, with code. Give the user a way to click what field to search, probably a combo box, then And Or buttons and a way to type in what to search for. A command button on the form can be used to set off the query. Its SQL statement would be something like this:

strSQL = "SELECT " & Me.NameOfControl & " FROM Table-Name "
strSQL = strSQL & " WHERE " & Me.NameOfControl & " Like *" & Me.NameOfControl & "*"

I have an example search form on the UtterAccess web site, if you would like to use it. Just go to www.utteraccess.com and search the code archive.

 

[Alansidman]

Look at this tutorial on Search forms. I think it will resolve your issue.

http://www.datapigtechnologies.com/flashfiles/searchform.html

Alan

 

[Dave_epic]

Hi
thanks for the replies. The tutorial was useful but it didn't exactly solve the problem.

Just to make it clear, I already have the form with command buttons which execute the query. I essentially have 3 keyword text boxes which search in 3 fields for records which must contain 1,2 or all of the keywords depending on how many keywords you enter. The sql for this query is below and it works OK.

The problem is want to make another query similar to the one above with a keyword search with 3 search boxes which search for either keyword in any record. This seems to work using 'or' instead of 'and' in the sql below if you search for all 3 keywords. The problem occurs if you leave one or two text boxes blank and it just returns all records.





SELECT column1, column2, column3
FROM table
WHERE [column1] Like "*" & [Forms]![name_of_form]![keyword1] & "*" AND [column1] Like "*" & [Forms]![name_of_form]![keyword2] & "*" AND [column1] Like "*" & [Forms]![name_of_form]![keyword3] & "*"
OR
WHERE [column2] Like "*" & [Forms]![name_of_form]![keyword1] & "*" AND [column2] Like "*" & [Forms]![name_of_form]![keyword2] & "*" AND [column2] Like "*" & [Forms]![name_of_form]![keyword3] & "*"
OR
WHERE [column3] Like "*" & [Forms]![name_of_form]![keyword1] & "*" AND [column3] Like "*" & [Forms]![name_of_form]![keyword2] & "*" AND [column3] Like "*" & [Forms]![name_of_form]![keyword3] & "*";

 

[Dave_epic]

Search form with wildcard parameters

Hi!

I've finished the seach form I was making and have attached it as zip. The form basically uses a wildcard parameter search to search 3 text fields in the table. There are 2 possible searches 'and' search and 'or' search for which I have used 2 separate queries.

I still can't figures out how to stop it returning all records when you leave one or two of the parameters blank during the 'or' search so I've had to add the condition that you put in an '@' instead of leaving it blank.

Regards

 

[Dave_epic]

Here is the zip file, didn't work before because it was .rar