Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-10-28
5
Medium Priority
?
370 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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
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.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

636 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