Solved

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

Posted on 2013-10-28
5
354 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
  • 3
  • 2
5 Comments
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 500 total points
Comment Utility
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
Comment Utility
I am using Informer by Entrinsik.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
Does it support the InStr function?
0
 

Author Comment

by:Newbi22
Comment Utility
Any SQL funtions
0
 

Author Closing Comment

by:Newbi22
Comment Utility
thank you
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now