separating a string for keyword search

[Guest]

I would like to build some code to separate a string entered by the user to
use as criteria for and sql query, but not sure on the easiest quickest way.

as of now, I have it so if the user enters one keyword in the search field,
the rowsource of the listbox contiaining the items being searched is amended
to include a WHERE statement where a combination of 3 fields is like * &
(criteria entered) & *. This Works fine...

If the user types in more than one word though, it doesnt work - for the
obvious reason that that exact string is non existent. I want to separate
the user criteria into invididual words (so separated at spaces) and add them
each to the WHERE statement (ie. where table.field & table.field2 like '*' &
(criteria) & '*' and table.field & table.field2 like ..........)

Maybe I am over complicating things, but I would like it if I could make
this happen.

Thanks in advance

 

[Guest]

You can pull apart the input string using the following built-in functions:
INSTR
LEN
LEFT$
MID$
RIGHT$

Look them up in Access Help

-Dorian

 

[Guest]

Thanks, but I know how to separate a string, I just can't figure how to
automate it to do it for each word in a string, then make each word a
variable that I can use as a SQL Where Criteria.