Trying to create a Query or Report Using Keyword Search

[FTR911]

I am using Access 2003 and fairly new to the project I have taken control of, I am creating sort of a knowledge base for a small Helpdesk, and I would like to be able to create a Keyword search that could locate records with similar Problems (example if a New Tech were to come across an issue that a customer could not run a spciifc application because she recives an error code, (providing the error code is already in the knowledge base ) the Tech could enter a keyword (erro 123xyz) and the record with the matching keyword will display.. ANy help ????

 

[RivetHed]

Create a search form with a textbox and a search button on it then create a query to show whatever details you want with a criteria of: Like "*" & [forms]![MyForm]![MyTextBox] & "*" for whichever field you're searching.

 

[FTR911]

"*" & [forms]![kbhd10]![Problem_keyword] & "*"

This is excatly what my search criteria looks like in my Query, What happens is all entered records appear.

KBHD10 is the name of the query
Probem_keyword is the name of the field wanting to Seacr through.

I know it is something simple what am I missing?? Thanks for all you help by the way...

 

[GingerR]

*" & [forms]![XXXXXX]![Problem_keyword] & "*"


XXXXXX should be a FORM NAME not the name of a query

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[FTR911]

Sorry my mistake KBHD10 is the name of the form, but the Search criteria is entered the results are, every entered record appears, Again I certainly appreciate the help from you guys..

 

[GingerR]

Do you have the keyword LIKE in there?


Like "*" & blah & "*"



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[RivetHed]

I'd double check your control reference, if it's returning every record then it sounds like it thinks that [problem_keyword] is blank. If the form is called kbhd10 and the control containing the search criteria is called problem_keyword and the form is open and problem_keyword has data in it that should work.

 

[FTR911]

You guys are graet bearing with me, and belive it or not I am learning, I was thinking , I have NOthing in my Form "KBHD10" that links the textbox where the keyword is entered to the query Criteria.
Example "*" & [forms]![kbhd10]![Problem_keyword] & "*"

That is the criteria line in my query,

And just out of curiosity , what does the [forms] point to??

 

[RivetHed]

[forms] points to the forms collection.

You don't need a reference to the query criteria in your form just have a command button on your form which runs the query. The query should then look at the form.

What do you have typed into the textbox named problem_keyword when you run the query?

 

[GingerR]

Again, do you have the word "LIKE" in there anywhere?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[FTR911]

Nope I do not have the word "LIKE" any where.. Should I ??

 

[GingerR]

see above

Like "*" & blah & "*"


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[dhookom]

FTR011,
If you can't figure this out, view the SQL view of your query and copy the SQL statement. Paste it into a reply in this thread. I have seen too many users place the criteria into a column/field which doesn't work.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[FTR911]

Now the criteria listed in my query is: Like "*" & [forms]![KBHD10]![Keyword] & "*"

with KBHD10 being the name of the Query
and Keyword being the name of the Text box, or should keyword be the name of the field I want to search????

 

[FTR911]

Now the criteria listed in my query is: Like "*" & [forms]![KBHD10]![Keyword] & "*"

with KBHD10 being the name of the Form (not Query)
and Keyword being the name of the Text box, or should keyword be the name of the field I want to search????

 

[GingerR]

So does it work?

if not, maybe "keyword" is an Access keyword (i don't know if it is or not) so maybe name the text box txtKeyword instead.

As for your question above, the thing in this statement is the name of the control (text box, combo box, whatever) that is on the form.

Forms!FormNameHere!TextBoxNameHere

Also, if it still doesn't work, paste the entire sql of your query here. If you do not know what that means, in the query design go to the VIEW menu and pick SQL, copy and paste that.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[FTR911]

Below is the SQL, and GingerR thank you soooo much for your help and instruction, you are so nice to be so helpfull..


SELECT KnowledgeBase.Problem_keyword, KnowledgeBase.Resolution, KnowledgeBase.Resolved_by, KnowledgeBase.Notes
FROM KnowledgeBase
WHERE (((KnowledgeBase.Problem_keyword) Like "*" & [forms]![KBHD10]![Problem_keyword] & "*"))
ORDER BY KnowledgeBase.Problem_keyword DESC;

 

[RivetHed]

The SQL is correct. What does the query return?

 

[GingerR]

If it returns nothing, make a query that has hardcoded in the criteria something you know exists (substitute in ZZZ below):

Like "*ZZZ*"

and make sure it works.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[FTR911]

Query returns a form with list of all entries, the query does'nt filter the keyword entries..