Separating an indeterminate number of words and searching on them

Posted on 2011-02-10
Last Modified: 2012-05-11
I have a form with a keyword search box (there are other things that are included in the search on the form ... checkboxes and such).

The keyword search box instructs users to enter one or more search terms separated by commas.  How can I (in code) take apart the search string (without knowing really how long it is)... at the comma, remove the spaces, and search a table ("MyTable") for each term? (I have a radio button that allows users to select "AND" or "OR" and I'll incorporate that info into the query i build in code).

Thanks for your help.
Question by:epuglise
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
LVL 11

Accepted Solution

Runrigger earned 500 total points
ID: 34866517
Declare and array string

Dim sArray() as String

Split the string the users enters by comma;

sArray = Split(userstring,",")

For n = 1 to Ubound(sArray)

sSearch = sArray(n)
Perform serach hare

next n

Author Comment

ID: 34866558
ok i had no idea there was a "split" function!  Great testing this now!

LVL 11

Expert Comment

ID: 34866580
you can even join them back together;

sOtherString = Join(sArray," AND ")

sOtherString = Join(sArray," OR ")
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.


Author Comment

ID: 34866654
OK that did it.  Just one thing... I had to change one line:

for n = 0 to ubound(sArray)

in order to get the first search term and I incorporated a "trim" function to get rid of the spaces.

Great!! Thanks for the new function!

Author Comment

ID: 34866669
Excellent... thanks for the And / Or tip!  "Bonus" kudos :)
LVL 11

Expert Comment

ID: 34866694
Sorry about that, I sometimes  forget that arrays are zero based!

Thanks for the grading

Author Comment

ID: 34866741
No problem... what a tidy little function Split is.  Saved me a bunch of brain cells... and I liked the method of gluing stuff back together so it all worked out ... i just wanted to post the minor correction for future users :)
LVL 11

Expert Comment

ID: 34869006
the function is great for doing a search and replace;

Function SearchAndReplace(sString as string,sSearchValue as string,sReplaceValue as String) as String
SearchAndReplace = Join(Split(sString,sSearchValue),sReplaceValue)
End Function

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question