[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Separating an indeterminate number of words and searching on them

Posted on 2011-02-10
8
Medium Priority
?
279 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
  • 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 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