?
Solved

SQP Operators Like, IN, Contains.....

Posted on 2013-10-28
5
Medium Priority
?
368 Views
Last Modified: 2013-11-24
Hello,

I am working on the following code. I am having problems with the WHERE clause...This part....

WHERE [CDE Book of Business].[Policy Exec] Like (<<[Text]+>>)

Is there a operator like CONTAINS... Where it is only part of the text to look at??

Think of it like searching a customer list where the business name contains the word BAR OR Restaurant. But I do not want it hard coded. The program I use recognizes the << >> as a prompt and fills in the rest.





SELECT DISTINCTROW [CDE Book of Business].[Policy Exec], [CDE Book of Business].[Account Name], Sum([CDE Book of Business].[Total Cost]) 

AS [Sum Of Total Cost], Sum([CDE Book of Business].[Agency Commission]) AS [Sum Of Agency Commission]


FROM [CDE Book of Business]

WHERE [CDE Book of Business].[Policy Exec] Like (<<[Text]+>>)

AND

([CDE Book of Business].[Policy Status] Is Null 

OR 

[CDE Book of Business].[Policy Status] in ('Renewed', 'Rewritten' ,'Active'))



GROUP BY [CDE Book of Business].[Policy Exec], [CDE Book of Bu

Open in new window

SELECT DISTINCTROW [CDE Book of Business].[Policy Exec], [CDE Book of Business].[Account Name], Sum([CDE Book of Business].[Total Cost]) 

AS [Sum Of Total Cost], Sum([CDE Book of Business].[Agency Commission]) AS [Sum Of Agency Commission]


FROM [CDE Book of Business]

WHERE [CDE Book of Business].[Policy Exec] Like (<<[Text]+>>)

AND

([CDE Book of Business].[Policy Status] Is Null 

OR 

[CDE Book of Business].[Policy Status] in ('Renewed', 'Rewritten' ,'Active'))



GROUP BY [CDE Book of Business].[Policy Exec], [CDE Book of Bu

Open in new window

0
Comment
Question by:Michael Franz
[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
  • 3
  • 2
5 Comments
 
LVL 31

Accepted Solution

by:
Helen Feddema earned 2000 total points
ID: 39607208
Maybe the InStr function would work here.  It searches for a string inside another string, and give the position where it starts if found.  Here is an example of address parsing using InStr and other string manipulation functions:

SELECT tblContactsTest.WholeAddress, Left([WholeAddress],InStr([WholeAddress],",")-1) AS Street, Mid([WholeAddress],InStr([WholeAddress],",")+1,([StatePos]-[StreetPos]-4)) AS City, Mid([WholeAddress],Len([WholeAddress])-7,2) AS State, Right([WholeAddress],5) AS Zip, InStr([WholeAddress],",")-1 AS StreetPos, Len([WholeAddress])-7 AS StatePos
FROM tblContactsTest;

This is for Access -- what program are you using?
0
 

Author Comment

by:Michael Franz
ID: 39607216
I am using Informer by Entrinsik.
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 39607232
Does it support the InStr function?
0
 

Author Comment

by:Michael Franz
ID: 39607235
Any SQL funtions
0
 

Author Closing Comment

by:Michael Franz
ID: 39672705
thank you
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

777 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