?
Solved

Separating an indeterminate number of words and searching on them

Posted on 2011-02-10
8
Medium Priority
?
277 Views
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.
0
Comment
Question by:epuglise
[X]
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
8 Comments
 
LVL 11

Accepted Solution

by:
Runrigger earned 2000 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
0
 

Author Comment

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

0
 
LVL 11

Expert Comment

by:Runrigger
ID: 34866580
you can even join them back together;

sOtherString = Join(sArray," AND ")

sOtherString = Join(sArray," OR ")
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:epuglise
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!
0
 

Author Comment

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

Expert Comment

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

Thanks for the grading
0
 

Author Comment

by:epuglise
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 :)
0
 
LVL 11

Expert Comment

by:Runrigger
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
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

752 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