Keyword search in a query

[master_dragon_1]

Hi!

I would like to make a query that searches for keywords instead of an exact match.

Let's say that I have a record that has "mountain giant" as one of the fields. I want the query to pick this record out whether I put "Mountain", "Giant" or both, or only the some of the letters

Thanks alot

 

[monkeytunes]

What you need is a parameter query. In the criteria space in the desired field in your query, put the following: Like "*" & [Search by word:] & "*"

When you run the query, a message box will pop up that says "Search by word:", and then you could type in "mountain" or "goat" or even just type in one or two letters. To make it more/less specific, play around with those wildcards.

 

[master_dragon_1]

Ok. Can you please explain each part of what you have written as a command?

Like "*" & [Search by word:] & "*"

What does this exactly tell Access to do?

 

[monkeytunes]

Ok. Can you please explain each part of what you have written as a command?

Like "*" & [Search by word:] & "*"

What does this exactly tell Access to do?

"Like" - This is stating the quality of the critera you will define. Other options include "<" (less than), ">" (greater than), "=" (equals), Is or Is Not (used when working with possible Null values), etc.

* - This is a wildcard. It will return all values.

& - Concantination, in human terms it's exactly what it looks like: a big, fat "AND" to merge stuff into one field.

"[ ]" - Brackets, these tell Access to prompt the user for a value, and any text contained therein (with the exception of certain punctuation marks which Access reserves for specialized functions) will be presented to the user in a popup window.

Try searching Access Help for "parameter" for more help.

 

[master_dragon_1]

But won't this search fore only 3 words, as there are 2 wildcards and one prompt?

 

[monkeytunes]

It will only search for what you input. This might not be the end solution for you, but I'm sure it's going to be your starting point. Did you actually try using this yet? Give it a whirl, and I think it might answer your questions.

The first wild card will allow for variables before your input. So if I have ONLY the first wild card, and I run the query and enter "ountain" in the window that will pop up (remember, the "[ ]" brackets tell Access this is a parameter query, and it will bring up a pop up window for you with whatever text you put in the brackets), it will return EVERY record that has data ending in "ountain" for that field. It would return "mountain" and "bountain" and "dfshfskjfhountin" and "34034534ountain".

The second wild card will allow for variables after your input. So, following the example above, if you have ONLY the second wild card, it would return "ountain340983059" and "ountainsdlkfjslfk" and so on; any records that have "ountain" in the beginning of that field. Using both wild cards together will let you widen your query.

Seeing how the wild cards work, along with the qualifiers (i.e. Like, Is, Is Not, =, and so on) can help you open up or pare down your queries. From there, clever use of "or" (or the extra criteria spaces) can give you yet more options. Experiment, experiment, experiment!

Let us know how that works out for you!

 

[master_dragon_1]

Thanks alot, its working exactly how I wanted it to be. I was just asking so I knew how each of the commands worked.

To be honest, I really got worried when I searched the forums and found this:

http://www.access-programmers.co.uk/forums/showthread.php?t=63587&highlight=keyword+query

What is the difference between this thread, and the one above?

Thanks alot!

 

[monkeytunes]

Thanks alot, its working exactly how I wanted it to be. I was just asking so I knew how each of the commands worked.

To be honest, I really got worried when I searched the forums and found this:

http://www.access-programmers.co.uk/forums/showthread.php?t=63587&highlight=keyword+query

What is the difference between this thread, and the one above?

Thanks alot!

Well, the examples in the thread you found are using VB code to do most/all of the work. If you go to Design View on the forms, open the Properties Window of any ofthe command buttons, you will find [Event Procedure] listed under the "On Click" event. If you focus your cursor on the [Event Procedure] and then click on the "..." box, it will open the Visual Basic Editor, and you can see the code being used to perform the queries.

As always, there are usually more solutions to a task in Access than you'd think. It wouldn't have even occurred to me to use VB code to get those results...but there you have it, and they work. I imagine you could've also used command buttons and wizards with some tweaking on a form to merely filter your results instead of querying for them (although, depending on the length of your fields and the amount of records, you might see a performance hit during the inital form load).

 

[master_dragon_1]

Yeah. When I saw that, I nearly got a heart-attack! I was thinking "There has GOT to be an easier way of doing it!" And I found it! Thanks alot Monkeytunes!

 

[georgvh]

multifield keyword search

HI, Im trying to create a keyword search based on the data in several fields. The below command works well for searching one field, but I want to search several at once. Any help would be great. Thanks!

What you need is a parameter query. In the criteria space in the desired field in your query, put the following: Like "*" & [Search by word:] & "*"

When you run the query, a message box will pop up that says "Search by word:", and then you could type in "mountain" or "goat" or even just type in one or two letters. To make it more/less specific, play around with those wildcards.