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 ????

 

[FTR911]

Strangley enough when a known keyword is entered, Nothing shows up from the query below is the SQL Code


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

 

[GingerR]

SELECT [KnowledgeBase].[Problem_keyword], [KnowledgeBase].[Resolution], [KnowledgeBase].[Resolved_by], [KnowledgeBase].[Notes]
FROM KnowledgeBase
WHERE [KnowledgeBase].[Problem_keyword] Like "*Picis*" ORDER BY [KnowledgeBase].[Problem_keyword] DESC;


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]

Another test I would perform to trouble-shoot is to make sure the form control is returning the value you expect. With the KBHD10 form open, enter a value into the Problem_keyword text box. Then press Ctrl+G to open the debug window. Enter the following

? [forms]![KBHD10]!Problem_keyword

and press enter. Do you see the value you expected?

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]

 

[GingerR]

Also, maybe a dumb question, but are you actually typing anything into the text box on the form before you try to run the 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

 

[dhookom]

Another question: what are typical values stored in the KnowledgeBase.Problem_keyword field? I would expect you to search the Notes field unless when data is entered, specific key words are entered into the Problem_keyword field.

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]

Nope not adumb question , and yes I am entering values in teh Text box, A typical entry into the table is "Customer cannot login to Picis, customer

A typical entry in the text box would be "picis"

And upon runnng the Debug test "Runtime error 2465

 

[dhookom]

Error 2465 is "Application-defined or object-defined error" which suggest either your form isn't open or something is not spelled correctly. If you have an open form named [KBHD10] with a control named Problem_keyword, you would not get an error.

You should really find and use a naming convention.

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]

 

[GingerR]

When I did the debug.print suggested by Duane calling the wrong name of the text box, I got error 2465 "MS Access can't find the field 'txt1' referred to in your expression"; once I fix the debug.print statement, it runs fine. So I echo Duane is that it sounds like your text box is not named "Problem_Keyword" or your form is not named "KBHD10". Also I suggest you name your form something like "frmKeywordLookup" as well as remove spaces, underscores and other punctuation from your control names (i.e. txtProblemKeyword would be better). Could save some hassles in the future.

You could make a new query, with your table as the source. In the query design window, click in the CRITERIA row under the field with the notes in it, and right-click and pick BUILD; then you will know for sure what your controls are called.

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