Solved

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

Posted on 2009-04-03
2
213 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

737 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