Boolean search?

[elamadon0228]

Hello. I have a query set on my form to search for a keyword contained in my database. However, it searches for that term and it will not do a Boolean search. Is there a way to do this?

This is the code I'm using:

SELECT [DI Database].ID, [DI Database].Keyword, [DI Database].Question, [DI Database].Response


FROM [DI Database]
WHERE ((([DI Database].Keyword) Like "*" & [Enter the keyword you wish to search for (ex: Heart Failure)] & "*"));

That allows a user to press the search keyword button, type in their keyword, and it auto runs the query based on the entered term. A print preview of the corresponding report appears, and then when they close it, it returns to the standard form.

Any ideas on how to enhance it so, instead of typing in one keyword (or consecutive words side by side), you could do like "Inhaler AND Asthma" - where the results will pull up all questions that contain those two words only, and they don't have to be side by side in the database entry. Right now it will only search multiple words if they are side by side (Heart Failure) for example.

Thanks in advance!

[aytee111]

strTest = "aaa bbb ccc"
strTest = "Like ""*" & Replace(strTest, " ", "*") & """"
Like "*aaa*bbb*ccc"

[elamadon0228]

Dumb question, but where in my code do I insert that string? Sorry - still a newbie with this!

[aytee111]

You need to pass the string to the SQL instead of the SQL asking for it. I assume that the SQL above is the record source for the report?

- create a text box where they will enter the keyword(s)
- change the SQL for the report to read - WHERE Keyword Like ""*" & Replace(Forms!formname!textboxname, " ", "*") & """";

[John_G]

Using "Like" isn't going to work if you want to find two words which can both occur anywhere in the string and you don't know which comes first. What you need to do is first extract the two words from your search string to separate variables, then search for each of them independently.

Suppose your variables containing the two words to look for are "Word1" and "Word2". Your WHERE might look something like this:

"WHERE Keyword like '*" & word1 & "*' AND keyword like '*" & word2 & *'".

Note that this does not look for distinct words (surrounded by blanks) - it will also select substrings, so

"distinct" like '*tin*'" is True. Trying to use blanks in the search presents difficulties as well when the "Word1" is at the beginning or end of "Keyword", or there is punctuation to deal with.

[elamadon0228]

You need to pass the string to the SQL instead of the SQL asking for it. I assume that the SQL above is the record source for the report?

- create a text box where they will enter the keyword(s)
- change the SQL for the report to read - WHERE Keyword Like ""*" & Replace(Forms!formname!textboxname, " ", "*") & """";

So what I copied was the SQL code from the SQL view tab of the query. The way I have my form set up, there is a box where people can enter keywords in. The button they click, however, should auto generate a report, based on the query I'm trying to program. So I'm trying to setup the initial query at this point.

For example, for a non-Boolean search, this is the SQL code I used for my query:

SELECT [DI Database].ID, [DI Database].Question, [DI Database].Response
FROM [DI Database]
WHERE ((([DI Database].Question) Like "*" & [Enter the term you wish to search for (ex: Heart Failure)] & "*"));

That code pulls up any form entry that has the keyword entered by the individual doing the search. My problem is I can't get it to be Boolean in nature - I want to be able to narrow down the results by allowing "this AND that" searching (so that the search will return only questions in my database that contain BOTH entered terms, but not just if they are side by side as they are now.

I'm trying to do the entry you gave me, and I have played with it a few ways, but each time I get an operator error. I do want the query to prompt with "Enter the term you wish you search for (ex: copd AND inhalers)" when you click on the search button.

This is the way I have entered the code you provided - maybe I'm missing a step (again, I'm still very new at this).

Trial 1: *Syntax Error*
SELECT [DI Database].ID, [DI Database].Keyword, [DI Database].Question, [DI Database].Response
FROM [DI Database]
WHERE ((([DI Database].Keyword) Like "*" & Replace(DI Database!DI Database!Keyword, " ", "*") &"""";

Trial 2: *Syntax Error*
SELECT [DI Database].ID, [DI Database].Keyword, [DI Database].Question, [DI Database].Response
FROM [DI Database]
WHERE ((([DI Database].Keyword) Like "*" & Replace([DI Database].Keyword, " ", "*") &"""":


Trial 3: ** This time I don't get the same error - I get a data type mismatch in criteria expression error ***

SELECT [DI Database].ID, [DI Database].Keyword, [DI Database].Question, [DI Database].Response
FROM [DI Database]
WHERE ((([DI Database].Keyword) Like "*" & Replace([DI Database].Keyword,[DI Database].Question, " ", "*") &""""));

[aytee111]

What is a Boolean search, what is a non-Boolean search?

Why do you want the query to prompt for the keyword? You will have to do a routine in VBA which reads the SQL of the query and parses out the entry they made and then replace it with what you want and then save the query.

Or else you could show the user how to use the "*"!

[elamadon0228]

a Boolean search allows you to search for something like, "Cookies AND Peanut butter" or "Cookies AND Peanut butter AND chocolate" --> where it searches for content containing all words.

A non Boolean search is just simply searching for cookies peanut butter - unless you have those words side by side next to each other, the search won't show them as a finding, even if they are all in the same paragraph.


This is for a data collection form used system wide - impossible to teach all users how to run queries - most have never used access. Setting it up to default to a form view only and all searches are run through a report that, after they view/print, closes back out to the original form. Making this as user friendly as possible. I can't run queries all the time for people for general searches. However, I have locked the navigation bar to prevent them from messing with my queries and reports.

[aytee111]

I still don't get your process: the user enters keywords on the screen, presses a button, a query runs and asks for more keywords, a report opens. At what point does this query run?

How do you know if the user wants a Boolean search or a non-Boolean search?

[InsuranceGuy]

Simply put, you cannot accomplish the task you presented within a Query.

You can create some more sophisticated VBA that takes user input, determines the number of criteria that need to be tested dynamically, and updates your query definition to match--but accomplishing this is rarely worth it. Instead, make a form that gives you a reliable set of options instead of using the popup box and you can use a simple Query that allows for multiple criteria tests.

Good luck,

Jeff

[orange]

You may get some ideas from this example in the sample databases section