Have I ask this before?

[KenHigg]

I may have ask this before but I can't seem to find it if I did:

Is there a way to do something like:

If IsIn(strMyValue, "SomeText1","SomeText2","SomeText3") then
    Do stuff
end if

Where a function like IsIn() would check to see if the string is in the comma delimeted string.

???
Ken

 

[Mile-O]

use the InStr() function.

 

[KenHigg]

Thanks SJ. I may be able to get that to work. I thought I had seen something else...

Say if I need to do something like the following:

If strMyName = "Smith" then
   me!textbox = "Hello"
ElseIf  strMyName = "Jones" then
   me!textbox = "Hello"
ElseIf strMyName = "Harris" then
   me!textbox = "Hello"
Else
   strMyName = "Goodbye"
End if

???
kh

 

[pono1]

Ken,

Not sure what you have in mind exactly; perhaps it's this...

Select Case strMyName
    Case "Smith", "Jones", "Harris"
        TextBox = "Hello"
    Case Else
        TextBox = "Good Bye"
End Select

Regards,
Tim

 

[john471]

Ive whipped something up for you....

Ken,

I've whipped up a function for you to try...
All care, no responsibility....

First two parameters aer options to
a) Match Case - set to true for a case sensitive match
b) Match Entire Search String (examples below)

b.i) nIsIn(True, True, "why", "Who","why not", "WHY") = 0
b.ii) nIsIn(True, False, "why", "Who","why not", "WHY") = 2
b.iii) nIsIn(False, False, "why", "Who","why not", "WHY") = 2
b.iv) nIsIn(False, True, "why", "Who","why not", "WHY") = 3
b.v) nIsIn(False, False, "Never", "Who","why not", "WHY") = 0

you would use it something like

If nIsIn(true, true, strMyValue, "SomeText1","SomeText2","SomeText3") > 0 then
    Do stuff
end if

'or 

If nIsIn(True, True, strMyName, "Smith", "Jones", "Harris") > 0 then
   me!textbox = "Hello"
Else
   ' strMyName = "Goodbye" (SIC)
   me!textbox = "Goodbye"
End if

Public Function nIsIn(ByVal bMatchCase As Boolean, ByVal bMatchEntire As Boolean, ByRef szSought As String, ParamArray varSearchIn()) As Integer
        'Returns 0 if szSought not in varSearchIn, as restricted by bMatchCase and bMatchEntire
        'or the (one based) index number of the first array element to satisfy the requirements
        'or -1 if an error occurred
        
        On Error GoTo nIsIn_Err
        
        Dim nCounter As Integer
        Dim nIndexIncrement As Integer
        Dim nPos As Integer
        Dim nCompare As Integer
        
        'Assume negative result
        nIsIn = 0
        
        If bMatchCase = True Then
            nCompare = vbBinaryCompare
        Else
            nCompare = vbTextCompare
        End If
        
        'ParamArray is always Zero Based array in AC97, regardless of Option Base statement
        'However who knows how this may change in future versions....
        
        If LBound(varSearchIn) = 0 Then
            nIndexIncrement = 1
        ElseIf LBound(varSearchIn) = 1 Then
            nIndexIncrement = 0
        Else
            Err.Raise vbObjectError + 1001, "nIsIn", "Unusable Array Structure"
        End If
        
        For nCounter = LBound(varSearchIn) To UBound(varSearchIn)
            If Len(Nz(varSearchIn(nCounter))) > 0 Then
                If bMatchEntire Then
                    If StrComp(szSought, varSearchIn(nCounter), nCompare) = 0 Then
                        nIsIn = nCounter + nIndexIncrement
                        Exit Function
                    End If
                Else 'Not bMatchEntire
                    nPos = InStr(1, varSearchIn(nCounter), szSought, nCompare)
                    If nPos > 0 Then
                        nIsIn = nCounter + nIndexIncrement
                        Exit Function
                    End If
                End If
            Else 'varSearchIn(nCounter) is null or zero length string
                If szSought = vbNullString Then
                    nIsIn = nCounter + nIndexIncrement
                    Exit Function
                End If
            End If
        Next nCounter
        
nIsIn_Exit:
    Exit Function
    
nIsIn_Err:
    nIsIn = -1
    MsgBox "An error occurred in nIsIn" & vbCrLf & "Error Number : " & Err.Number & vbCrLf & "Error Description : " & Err.Description & vbCrLf & "Error Source : " & Err.Source, vbOKOnly + vbInformation, "nIsIn Error"
End Function

HTH

Regards

John.

 

[KenHigg]

Tim, I think that must've been what I remebered seeing somewhere - Thanks.

John, Interesting solution. In fact you gave me some ideas for another issue - Thanks

Ken

 

[IfButOnly]

Ken, were you thinking of the Choose function?

 

[KenHigg]

Not sure - let me read up on it...

Thanks,
Ken