Solved

Using LIKE and OR to look for more then one pattern in a field

Posted on 2009-04-03
2
212 Views
Last Modified: 2013-11-28
Hi all this is probably pretty simple but I dont work in SQL but 4gl

I have a sql statement that uses like to find entries that contain the term 'out' for a particular date range.

What I want to do is specify that I want to look for entries that contain the word 'out' OR 'in' OR 'pto'.

Looking at the code example below how would I change this with the following select statement.
Original working query:
 
query = "SELECT * FROM cal_events E INNER JOIN cal_dates D" _
	& " ON (E.event_id = D.event_id) " _
	& " WHERE (event_title LIKE '%out%') AND (event_date BETWEEN #" & Date & "# AND #" & Date & "#) AND (user_id = " _
	& Session(dataName & "User") & " OR " _
	& "private = 0) ORDER BY event_date"
 
 
I tried changing to:
 
query = "SELECT * FROM cal_events E INNER JOIN cal_dates D" _
	& " ON (E.event_id = D.event_id) " _
	& " WHERE (event_title LIKE '%out%') OR " _
	& "(event_title LIKE '%in%') & OR " _
	& "(event_title LIKE '%pto%') & AND " _ 
	& "(event_date BETWEEN #" & Date & "# AND #" & Date & "#) AND (user_id = " _
	& Session(dataName & "User") & " OR " _
	& "private = 0) ORDER BY event_date"
	
OR can I use a wild card to simplify something like:
 
query = "SELECT * FROM cal_events E INNER JOIN cal_dates D" _
	& " ON (E.event_id = D.event_id) " _
	& " WHERE (event_title LIKE '[out,in,pto]%') AND (event_date BETWEEN #" & Date & "# AND #" & Date & "#) AND (user_id = " _
	& Session(dataName & "User") & " OR " _
	& "private = 0) ORDER BY event_date"

Open in new window

0
Comment
Question by:binovpd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 17

Accepted Solution

by:
k_murli_krishna earned 50 total points
ID: 24063034
The first one you changed to is correct. Once cannot use as OR operator in a group for LIKE operator the way you do for IN operator. Since your words are part of bigger words/phrases/sentences you need to use LIKE and each LIKE separated by OR.
0
 

Author Closing Comment

by:binovpd
ID: 31566378
Thanks k_murli_krishna I got it to work.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

756 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