PROBLEM using CharIndex(), IF, OR etc- PLEASE HELP

[matthewwhaley@gmail.com]

What is the best way to essentially use the charindex(find) function
if the value is could be more than one variable (A or B or C)

I can't seem to get an "or", "if" or "select if" to work


Below is the code that works, I need 'GOV' to be either 'GOV' or 'FWD'
or 'LIB'

sqlstring = "SELECT Distinct substring([exposurename]," _
& Len(Worksheets( 4).Range("j5") & "_VAR_" _
&
Worksheets(4).R ange("C4").Offs et(Worksheets(4 ).Range("c3"), 0)) + 2 _
& ",charindex('GO V',[exposurename])-" &
Len(Worksheets( 4).Range("j5") _
& "_VAR_" &
Worksheets(4).R ange("C4").Offs et(Worksheets(4 ).Range("c3"), 0)) + 3 _
& ") AS Drivergrp2 " _
& "FROM mars.dbo.mroInv entoryProductGr eeks_Latest
" _
& "Where producttype = 'creditdefaults wap' " _
& "AND exposureName like '" &
Worksheets(4).R ange("j5") & "_VAR_" &
Worksheets(4).R ange("C4").Offs et(Worksheets(4 ).Range("c3"), 0) & "%' "
_

Much appreciation if you can help

[SB]

Re: PROBLEM using CharIndex(), IF, OR etc- PLEASE HELP

On May 11, 5:09 am, matthewwha...@g mail.com wrote:

What is the best way to essentially use the charindex(find) function
if the value is could be more than one variable (A or B or C)
>
I can't seem to get an "or", "if" or "select if" to work
>
Below is the code that works, I need 'GOV' to be either 'GOV' or 'FWD'
or 'LIB'
>
sqlstring = "SELECT Distinct substring([exposurename]," _
& Len(Worksheets( 4).Range("j5") & "_VAR_" _
&
Worksheets(4).R ange("C4").Offs et(Worksheets(4 ).Range("c3"), 0)) + 2 _
& ",charindex('GO V',[exposurename])-" &
Len(Worksheets( 4).Range("j5") _
& "_VAR_" &
Worksheets(4).R ange("C4").Offs et(Worksheets(4 ).Range("c3"), 0)) + 3 _
& ") AS Drivergrp2 " _
& "FROM mars.dbo.mroInv entoryProductGr eeks_Latest
" _
& "Where producttype = 'creditdefaults wap' " _
& "AND exposureName like '" &
Worksheets(4).R ange("j5") & "_VAR_" &
Worksheets(4).R ange("C4").Offs et(Worksheets(4 ).Range("c3"), 0) & "%' "
_
>
Much appreciation if you can help

Hi,
See if this works. You may have to play with double quotes to get it
working since I do not know VB or excel programming.

sqlstring = "SELECT Distinct substring([exposurename]," _
& Len(Worksheets( 4).Range("j5") & "_VAR_" _
&
Worksheets(4).R ange("C4").Offs et(Worksheets(4 ).Range("c3"), 0)) + 2 _
& ",
isnull(nullif(i snull(nullif(ch arindex('GOV',[exposurename])-" &
Len(Worksheets( 4).Range("j5") _
& "_VAR_" &
Worksheets(4).R ange("C4").Offs et(Worksheets(4 ).Range("c3"), 0)) + 3 _
& ",0), charindex('FWD' ,[exposurename])-" &
Len(Worksheets( 4).Range("j5") _
& "_VAR_" &
Worksheets(4).R ange("C4").Offs et(Worksheets(4 ).Range("c3"), 0)) + 3 _
& "),0), charindex('LIB' ,[exposurename])-"
&
Len(Worksheets( 4).Range("j5") _
& "_VAR_" &
Worksheets(4).R ange("C4").Offs et(Worksheets(4 ).Range("c3"), 0)) + 3 _
& ")
) AS Drivergrp2 " _
& "FROM mars.dbo.mroInv entoryProductGr eeks_Latest
" _
& "Where producttype = 'creditdefaults wap' " _
& "AND exposureName like '" &
Worksheets(4).R ange("j5") & "_VAR_" &
Worksheets(4).R ange("C4").Offs et(Worksheets(4 ).Range("c3"), 0) & "%' "

[SB]

Re: PROBLEM using CharIndex(), IF, OR etc- PLEASE HELP

On May 11, 11:02 am, SB <othell...@yaho o.comwrote:

On May 11, 5:09 am, matthewwha...@g mail.com wrote:
>
>
>
>
>

What is the best way to essentially use the charindex(find) function
if the value is could be more than one variable (A or B or C)

>

I can't seem to get an "or", "if" or "select if" to work

>

Below is the code that works, I need 'GOV' to be either 'GOV' or 'FWD'
or 'LIB'

>

sqlstring = "SELECT Distinct substring([exposurename]," _
& Len(Worksheets( 4).Range("j5") & "_VAR_" _
&
Worksheets(4).R ange("C4").Offs et(Worksheets(4 ).Range("c3"), 0)) + 2 _
& ",charindex('GO V',[exposurename])-" &
Len(Worksheets( 4).Range("j5") _
& "_VAR_" &
Worksheets(4).R ange("C4").Offs et(Worksheets(4 ).Range("c3"), 0)) + 3 _
& ") AS Drivergrp2 " _
& "FROM mars.dbo.mroInv entoryProductGr eeks_Latest
" _
& "Where producttype = 'creditdefaults wap' " _
& "AND exposureName like '" &
Worksheets(4).R ange("j5") & "_VAR_" &
Worksheets(4).R ange("C4").Offs et(Worksheets(4 ).Range("c3"), 0) & "%' "
_

>

Much appreciation if you can help

>
Hi,
See if this works. You may have to play with double quotes to get it
working since I do not know VB or excel programming.
>
sqlstring = "SELECT Distinct substring([exposurename]," _
& Len(Worksheets( 4).Range("j5") & "_VAR_" _
&
Worksheets(4).R ange("C4").Offs et(Worksheets(4 ).Range("c3"), 0)) + 2 _
& ",
isnull(nullif(i snull(nullif(ch arindex('GOV',[exposurename])-" &
Len(Worksheets( 4).Range("j5") _
& "_VAR_" &
Worksheets(4).R ange("C4").Offs et(Worksheets(4 ).Range("c3"), 0)) + 3 _
& ",0), charindex('FWD' ,[exposurename])-" &
Len(Worksheets( 4).Range("j5") _
& "_VAR_" &
Worksheets(4).R ange("C4").Offs et(Worksheets(4 ).Range("c3"), 0)) + 3 _
& "),0), charindex('LIB' ,[exposurename])-"
&
Len(Worksheets( 4).Range("j5") _
& "_VAR_" &
Worksheets(4).R ange("C4").Offs et(Worksheets(4 ).Range("c3"), 0)) + 3 _
& ")
) AS Drivergrp2 " _
& "FROM mars.dbo.mroInv entoryProductGr eeks_Latest
" _
& "Where producttype = 'creditdefaults wap' " _
& "AND exposureName like '" &
Worksheets(4).R ange("j5") & "_VAR_" &
Worksheets(4).R ange("C4").Offs et(Worksheets(4 ).Range("c3"), 0) & "%' "- Hide quoted text -
>
- Show quoted text -

There is an elegant way to do this. However your excel ranges are too
big to accommodate that solution. Basically, you switch your string
positions in charindex function. Therefore, instead of looking for GOV
in target string, you take the whole target string and match with GOV.
It is something like:
Strip out gov etc: Substring(targe t_string,...)
Then you do: charindex('stri pped string', 'GOV,FWD,LIB')
And if your target string is small (in your case probably it isn't)
then you can directly use it in charindex as: charindex('sour ce
string', 'GOV,FWD,LIB')
Maybe in your excel you can create an extra column where you store the
stripped column then you can just match it with 'GOV,FWD,LIB'.
HTH.