468,525 Members | 2,191 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,525 developers. It's quick & easy.

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

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.

Feb 22 '08 #1
5 2072
446 Expert 256MB
I suppose you know that the InStr() function returns the position of the first matching character ?
Expand|Select|Wrap|Line Numbers
  1. MyPos = InStr(strText , " ")
You could then use the Left() function to chunk off the first word then add that to your StrCriteria.
Expand|Select|Wrap|Line Numbers
  1. strCriteria = "Like " & Left(strText, MyPos) & "*"
Then shorten strText for the next loop
Expand|Select|Wrap|Line Numbers
  1. 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
Feb 22 '08 #2
32,098 Expert Mod 16PB
If the operator knows to use a delimiter character (EG , ; : - etc) between the search phrases then you can process through the string using :
Expand|Select|Wrap|Line Numbers
  1. 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).
Feb 24 '08 #3
32,098 Expert Mod 16PB
How you'd process through all that would be something like the following :
Expand|Select|Wrap|Line Numbers
  1. Private Function GetFilter(strField As String, strControl As String) As String
  2.   Dim intN As Integer
  4.   If UBound(Split(strControl, ";")) < 0 Then exit Function
  5.   For intN = 0 To UBound(Split(strControl, ";"))
  6.     GetFilter = GetFilter & " And ([" & strField & "] Like '*" & _
  7.                             Split(strControl, ";")(intN) & "*')"
  8.   Next intN
  9.   GetFilter = Mid(GetFilter, 6)
  10. 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.
Feb 24 '08 #4
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.

Feb 25 '08 #5
32,098 Expert Mod 16PB
It was interesting NJ - and it's always nice when someone is NOT simply looking for solutions to be done for them ;)
Good luck :)
Feb 25 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Krishna Srinivasan | last post: by
1 post views Thread by cassandra.flowers | last post: by
4 posts views Thread by Mike | last post: by
1 post views Thread by fmendoza | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.