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

[matthewwhaley]

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).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0)) + 2 _
& ",charindex('GOV',[exposurename])-" &
Len(Worksheets(4).Range("j5") _
& "_VAR_" &
Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0)) + 3 _
& ") AS Drivergrp2 " _
& "FROM mars.dbo.mroInventoryProductGreeks_Latest
" _
& "Where producttype = 'creditdefaultswap' " _
& "AND exposureName like '" &
Worksheets(4).Range("j5") & "_VAR_" &
Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0) & "%' "
_

Much appreciation if you can help

[SB]

On May 11, 5:09 am, [e-mail] 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).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0)) + 2 _
& ",charindex('GOV',[exposurename])-" &
Len(Worksheets(4).Range("j5") _
& "_VAR_" &
Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0)) + 3 _
& ") AS Drivergrp2 " _
& "FROM mars.dbo.mroInventoryProductGreeks_Latest
" _
& "Where producttype = 'creditdefaultswap' " _
& "AND exposureName like '" &
Worksheets(4).Range("j5") & "_VAR_" &
Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("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).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0)) + 2 _
& ",
isnull(nullif(isnull(nullif(charindex('GOV',[exposurename])-" &
Len(Worksheets(4).Range("j5") _
& "_VAR_" &
Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0)) + 3 _
& ",0), charindex('FWD',[exposurename])-" &
Len(Worksheets(4).Range("j5") _
& "_VAR_" &
Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0)) + 3 _
& "),0), charindex('LIB',[exposurename])-"
&
Len(Worksheets(4).Range("j5") _
& "_VAR_" &
Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0)) + 3 _
& ")
) AS Drivergrp2 " _
& "FROM mars.dbo.mroInventoryProductGreeks_Latest
" _
& "Where producttype = 'creditdefaultswap' " _
& "AND exposureName like '" &
Worksheets(4).Range("j5") & "_VAR_" &
Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0) & "%' "

[SB]

On May 11, 11:02 am, SB <[e-mail]:

On May 11, 5:09 am, [e-mail] 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).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0)) + 2 _
& ",charindex('GOV',[exposurename])-" &
Len(Worksheets(4).Range("j5") _
& "_VAR_" &
Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0)) + 3 _
& ") AS Drivergrp2 " _
& "FROM mars.dbo.mroInventoryProductGreeks_Latest
" _
& "Where producttype = 'creditdefaultswap' " _
& "AND exposureName like '" &
Worksheets(4).Range("j5") & "_VAR_" &
Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("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).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0)) + 2 _
& ",
isnull(nullif(isnull(nullif(charindex('GOV',[exposurename])-" &
Len(Worksheets(4).Range("j5") _
& "_VAR_" &
Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0)) + 3 _
& ",0), charindex('FWD',[exposurename])-" &
Len(Worksheets(4).Range("j5") _
& "_VAR_" &
Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0)) + 3 _
& "),0), charindex('LIB',[exposurename])-"
&
Len(Worksheets(4).Range("j5") _
& "_VAR_" &
Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("c3"), 0)) + 3 _
& ")
) AS Drivergrp2 " _
& "FROM mars.dbo.mroInventoryProductGreeks_Latest
" _
& "Where producttype = 'creditdefaultswap' " _
& "AND exposureName like '" &
Worksheets(4).Range("j5") & "_VAR_" &
Worksheets(4).Range("C4").Offset(Worksheets(4).Ran ge("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(target_string,...)
Then you do: charindex('stripped 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('source
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.