custom search function

[teel73]

I am trying to provide the user a custom search feature. They want to enter a keyword or phrase and search 3 memo fields and filter the form base on the records found. they also want to be able to search the whole phrase or any part of the phrase.

I have a like expression for any part of the phrase but I when I set it up for whole phrase it doesn't work. Even if I run a simple query and use

For example: There's an acronym the user is looking for : ACA

If I set my query up like this: [field1] like "*ACA*" or [field2] like "*ACA*" or [field3] like "*ACA*"

it not only finds records with that acronym but it also finds records where that combination is found in a word, for instance vacate.

How can I set up my query to find the whole word?

 

[plog]

Your'e kind of fishing with a net with big holes--You don't know where in the memo field your string is at, so you have to use wildcards (*) to find it. But doing so lets too much stuff in. You could make your net more restrictive by having your search string be preceded by and followed by spaces:

[field1] like "* ACA *" or [field2] like "* ACA *" or [field3] like "* ACA *"

Of course now your net won't let in results where your string is immediately followed by punctuation (i.e. ACA., ACA;, ACA,).

So maybe the answer is just preceding spaces

[field1] like "* ACA*" or [field2] like "* ACA*" or [field3] like "* ACA*"

Now ' Academy' and 'Acacia' and 'Acaulescent' are coming through. I don't know, how tight/loose do you want the net? And how many references to stemless plants are in your data?

 

[Mihail]

Another idea:
Use an external function.
The InStr() function will say you where "ACA" is founded.
Dim lngPos As Long
lngPos = InStr(....)
Now you can do tests like:
IF lngPos = 0 Then "ACA" is not founded.
IF lngPos = 1 Then "ACA" has nothing before
IF lngPos + Len("ACA") = Len(AllString) then "ACA" has nothing after
IF "ACA" has something before, test the character that is in that position (lngPos - 1). If this character is NOT a or A or b or B or.... then the word start with "ACA"

The same you can test the character after "ACA"

Finally, if "ACA" pass all tests then YEEESSSS !!!!! It is a word !!!!!!

Don't be wary. It is easier for you to implement this logic than for me to explain in English

Good luck !

 

[stopher]

Like "*[!A-Z]ACA[!A-Z]*" Or Like "*ACA[!A-Z]*" Or Like "*[!A-Z]ACA" Or Like "ACA"