extract text from comma seperated values in a text box

By carterlangley ·
hi guys, girls

i have users entering words in a text box seperated by commas. i need to use those words as values in a search. there might be only one word, but then there might be twenty or only three. i need my code to extract the words there and then to build a sql statement that is going to be used to search the relevant area of the table for matching values. i am using access 2003.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Is it possible to change your table structure?

by Absolutely In reply to extract text from comma s ...

If you can move that field to a subdatasheet or something that can expand to as many rows as necessary per record in your primary table, you won't have to separate with commas. Probably since you asked the question you have already crossed that bridge, but just in case there is an easier solution, I have to ask...

Collapse -

Try this.

by bsmith523 In reply to extract text from comma s ...

Instead of parsing out the data just use the whole string and change the comma. You will need to put in some code incase only 1 item is entered. This also will have problems if someone puts in multiple spaces after the comma or multiple commas, etc...

1) assume the input is: "dog, cat"
2) Add it to the sql statement. Don't forget to use single quotes around the text "select field1 from table 1 where ((field1 like '" & input & "*'));"
3) Replace the comma and space after the comma with "*') or (field1 like '".
4) It should now read as follows:
select field1 from table1 where ((field1 like 'dog*') or (field1 like 'cat*'));

There you go.

Related Discussions

Related Forums