Access SQL- SELECT WHERE text exists in the string

Posted on 2011-05-10
Medium Priority
Last Modified: 2012-05-11

Hi. I want to convert the following SQL statement so that if "PARENT" exists [Apllicable to] it selects that record. Do I use the Instr function or is there a better function? In other words if
[Applicable to] contained "PARENT,STAFF" then it would select that records

sSQL= "SELECT * FROM [Table1] WHERE [Applicable to] = ' PARENT'"
Question by:Murray Brown
LVL 39

Accepted Solution

Pratima Pharande earned 1000 total points
ID: 35727279
sSQL= "SELECT * FROM [Table1] WHERE [Applicable to] like '%PARENT%'"
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 35727281
sSQL= "SELECT * FROM [Table1] WHERE [Applicable to] LIKE '*PARENT*' " 
or better logic:

sSQL= "SELECT * FROM [Table1] WHERE ',' & [Applicable to] & ',' LIKE '*,PARENT,*'"

you might need to use % instead of * if it's ms sql server and not ms access, and + instead of the & also ...


Author Closing Comment

by:Murray Brown
ID: 35727295
Thanks very much

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
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…

862 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