By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,520 Members | 1,268 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,520 IT Pros & Developers. It's quick & easy.

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

P: 44
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
Share this Question
Share on Google+
5 Replies

Expert 100+
P: 446
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

Expert Mod 15k+
P: 31,768
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

Expert Mod 15k+
P: 31,768
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

P: 44
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

Expert Mod 15k+
P: 31,768
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.