I have a field (called parentID) in a database with numeric values separated by a comma
e.g. 2,12,18,22,40

I would like to find records based on a user selection that will look up the values in this comma list. I use this statement at the moment:

SELECT * FROM QUESTIONS WHERE parentID LIKE '%' & '2' & '%' The only problem is it finds records whenever 2 occurs such as 22,24,32,42 and
not just the 2.

How do I only find the record with 2 or is it impossible due to the
way I have built the field data?

First off, that's not the correct way to set up the relationship, should have used an intermediate table to create a many-to-many relationship.

However you can bodge your way round it, the answer is to not use the % wildcard symbols and to include the commas that separate the values too. For example:

SELECT * FROM QUESTIONS WHERE parentID LIKE ',2,'

SELECT * FROM QUESTIONS WHERE parentID LIKE ',3,'

SELECT * FROM QUESTIONS WHERE parentID LIKE ',23,'

etc..

Regards

Owen.

-------------------------------------------------------------------------------------------
Used programming books and web development software for sale (UK only): www.tdsf.co.uk/tdsfdemo/Shop.htm

Developer services and tutorials: www.drdev.net

Multiple Parameter UD4 / Access 2000 Search Tutorial: www.tdsf.co.uk/tdsfdemo/

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
First off, that's not the correct way to set up the relationship, should have used an intermediate table to create a many-to-many relationship.
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

What is one of these? I am using Access to run the database.

Have a look at this: www.tdsf.co.uk/tdsfdemo/Tutorial04.htm, shows how to set up a many-to-many relationship.

Regards

Owen.

-------------------------------------------------------------------------------------------
Used programming books and web development software for sale (UK only): www.tdsf.co.uk/tdsfdemo/Shop.htm

Developer services and tutorials: www.drdev.net

Multiple Parameter UD4 / Access 2000 Search Tutorial: www.tdsf.co.uk/tdsfdemo/