Searching a field with comma separated string...

MissJulie

New member
Local time
Today, 05:59
Joined
Feb 2, 2010
Messages
8
.. Hi peeps. I have search for guidance but google isnt doing its job like it used to and would like to reach out to yall for help.

My problem is below and i am doing this in access 2007.

I have a table Hobbies:

Hobbies
|names||city||DOB||keywords||

Records for example, would be like:

|James Ross||London||12/12/85||cricket, snooker, electornics, building||
|David Magambo||Manchester||01/09/87||gaming, biking, poker||
|Hans Gruber||Berlin||01/01/73||smoking, programming, gaming, tennis||

I want to be able to search the comma separated string for words. for example if i were to search for the people with the keywords "gaming" it would bring up David and Hans recrods, or if i were to search for keywords "cricket" or "tennis" it would bring up James and Hans records..

Im guessing it would be an sql query? and it might be trivial but i have no idea how to go about this and would really appreciate the help.

Thanks, Julie.
 
You can use the like operator in your query so if you were to search for gaming it would be

LIKE "*"& yourfieldname &"*"

Where your fieldname is the textbox that you are entering the search criteria in.
 
that may be ok for a single word. but what if i wanted to search two or three keywords. and what about the commas and accuracy?
 
Well, this design is not normalized correctly if you're worried about accuracy and multiple keywords.

You can search for multiples by using "OR" between "LIKE" comparisons:
Code:
select * from Hobbies
where
keywords LIKE '*gaming*'
OR
keywords LIKE '*tennis*'
;
Depending on how large your database is, this could be dog slow. One fix for slowness is to normalize the database design.

Also, if the person inputting data into the "keywords" field types one of the keywords wrong, this approach will miss it, unless you include the mis-spelling in your criteria.
 
Thanks people that works fine!

Would it be fairly staright forward to implement that in a form? or is using SQL more of a robust solution?
 

Users who are viewing this thread

Back
Top Bottom