Like Criteria from atext box.

[Torinjr]

I have a form where I am hoping that i can type in keywords into a text box which will then run a query that finds these key words from the data field.



For example: The user types "car license" into the text box, the query will then search the descriptions field and displays all descriptions that contain "car license" i.e "I have my car license" but not "I have a car and i have a license".

So far i have tried setting the Criteria using the Like command to pull the value entered in the text box. I tried including the asterixs so that the text can be contained any where within the description.
Criteria: Like "*[Forms]![Applicants]![KeywordTextbox]*"

Is this possible and am i on the right track, What should my Criteria be? Cheers.

[CJ_London]

your code does not appear tobe correct, it should be

Like "*" & [Forms]![Applicants]![KeywordTextbox] & "*"

but to answer your question, train your users to use the *

so if they want to find records which have both car and license in it (in that order), tell them to type 'car*license'

if you want individual searches so it will also find 'I have a license and i have a car' then you will need some code to break the string down into individual components and build a more complex criteria

Be aware that putting a * at the beginning of your search string forces Access to do a sequential search which is significantly slower than one on an indexed field. For relatively small data volumes it is not a really problem but as your db grows you will see performance suffer.

I'm not saying never code the initial * but use it wisely. I train my users to use it themselves when required so I do not include *'s in my code. Typically they are searching for a name so looking for 'smith' they will start with a 's' anyway, all an initial * would do is slow it down.

[Torinjr]

Sorry im not sure i explained myself fully let me use a better example closer to what im trying to do.

Item Description in the table is: Lanterns - 200 mm diameter 4 aspect LED lanterns.

The user wants to search for this so tries typing in a keyword that is contained within the description. i.e "4 aspect LED". The query will then show all the item descriptions that contains 4 aspect LED from any part of the description.

I didnt understand what you meant by train your users to use the *?

[CJ_London]

Item Description in the table is: Lanterns - 200 mm diameter 4 aspect LED lanterns.

The user wants to search for this so tries typing in a keyword that is contained within the description. i.e "4 aspect LED". The query will then show all the item descriptions that contains 4 aspect LED from any part of the description.

then the correction to your criteria should do the trick

Like "*" & [Forms]![Applicants]![KeywordTextbox] & "*"

I didnt understand what you meant by train your users to use the *?

For your purposes it probably doesn't matter. training them to use it means they could also find 'Lanterns....LED'

[Torinjr]

Hi Ajax thanks for the correction. That was all i needed. And i understand what you mean now.
Cheers