Solved

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

Posted on 2013-10-28
5
365 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:Newbi22
[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 500 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:Newbi22
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:Newbi22
ID: 39607235
Any SQL funtions
0
 

Author Closing Comment

by:Newbi22
ID: 39672705
thank you
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query Grouping Question Record Inserts 9 62
Oracle Date 6 35
Access Need to add combo box to sub form 10 46
Speed up Select Top n... Query 9 30
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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 …
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 …

739 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