Read a text box, get all the words, and create a Search String

[NJonge01]

Hi, I haven't even tried to code this yet, but I need to create a "search" string that would work like this:

Read this: strText = [Word1 Word2 Word3]
Create this string
StrCriteria = Like "*Word1*" and Like "*Word2*" and Like "*Word3*"

Even better would be:
Read this: strText = [Word1 "Word2 Word3"]
create this string
StrCriteria = Like "*Word1* and Like "*Word2 Word3*"

I'm not asking anyone to code it for me... just need ideas on how to entable the words, and accurately tell the logic how to delimit the words... from there I think I can read the table out to the string that will be used as the criteria.

Thanks!

[sierra7]

Hi
I suppose you know that the InStr() function returns the position of the first matching character ?

MyPos = InStr(strText , " ")

You could then use the Left() function to chunk off the first word then add that to your StrCriteria.

strCriteria = "Like " & Left(strText, MyPos) & "*"

Then shorten strText for the next loop

strText = Mid(StrText, MyPos)

This needs testing to ensure it is not MyPos+1 to get rid of the space. but you get the drift?

Somewhere in the loop will be a test for character 1 = (") in which case you would split-off and find the next quote (") rather than next space. etc
S7

[NeoPa]

If the operator knows to use a delimiter character (EG , ; : - etc) between the search phrases then you can process through the string using :

Split(Me.Control, ";")(intN)

...where ";" is the separator character and intN (or even literal integer) refers to the Nth item in the array.

Remember to use single-quotes (') for SQL strings and double-quotes (") for VBA strings (which can contain the SQL ones).

[NeoPa]

How you'd process through all that would be something like the following :

Private Function GetFilter(strField As String, strControl As String) As String
  Dim intN As Integer

  If UBound(Split(strControl, ";")) < 0 Then exit Function
  For intN = 0 To UBound(Split(strControl, ";"))
    GetFilter = GetFilter & " And ([" & strField & "] Like '*" & _
                            Split(strControl, ";")(intN) & "*')"
  Next intN
  GetFilter = Mid(GetFilter, 6)
End Function

PS. I appreciate that you weren't asking for the code so I expect you're competent enough to learn by reviewing this example. If you need to ask any questions though, feel free to.

[NJonge01]

wow... good stuff! I didn't get a chance to look at this over the weekend so I showed up at work today and I see this wonderful information and advice.

I'll work with the information you've shared and respond with my results.

Thanks so much for taking the time to consider my question and for providing an excellent and educational response.

NJ

[NeoPa]

It was interesting NJ - and it's always nice when someone is NOT simply looking for solutions to be done for them ;)
Good luck :)