Is there any VB6 process like the SQL "IN" operator? 3

[jbradley]

Is there any way in VB6 to simplify a long series of If-Then operations

[b][blue]
If String1 = String2 Or _
   If String1 = String3 . . . Then
[/blue][/b]

by doing something like a SQL query

[b][blue]
IF String1 IN (String2, String3 . . .)
[/blue][/b]

 

[judgeh59]

not sure if this helps...you could try a select case where the case statement can have multiple strings and then execute a particular piece of code...I did a quick and dirty program and it seemed to work...

HTH

Ernest

Be Alert, America needs more lerts

 

[Andrzejek]

Or...:

If [blue]InStr[/blue]("*" & string2 & "*" & string3 & "*", "*" & String1 & "*") Then
    [green]'String1 is in one of the other strings[/green]
Else
    [green]'Sorry, no cigar[/green]
End If

Have fun.

---- Andy

 

[SkipVought]

sorry, the else SHOULD be ....

'close but no cigar

;-)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jbradley]

Thanks to both of you. I went with the Select Case method because I saw that first but I like the InStr method just as well.

 

[jbradley]

Ok. So I had the bright idea that I would put my matching strings in a text file and read them in when the process runs. That way if the list changes I don't need to open the code to change it. My original Case line looks like this:

[b][blue]        Case "String1", "String2", "String3", "String4", "String5"
[/blue][/b]

My file is formatted with one string on each line, each in quotes:

[b][blue]"String1"
"String2"
"String3"
"String4"
"String5"
[/blue][/b]

I read the file into a string variable:

[b][blue]    Dim tsControlFile       As TextStream
    Dim strControlFile      As String
    Dim strSelectString     As String
    Set tsControlFile = fso.OpenTextFile(App.Path & "\ControlFile.txt", ForReading)
    Do While Not tsControlFile.AtEndOfStream
        strControlFile = tsControlFile.ReadLine
        If strSelectString = "" Then
            strSelectString = strControlFile
        Else
            strSelectString = strSelectString & ", " & strControlFile
        End If
    Loop
[/blue][/b]

Then I do my Select clause:

[b][blue]        Select Case Trim(strRecordType)
            Case strSelectString
[/blue][/b]

The problem is that when I do the Select clause the normal way it works fine but when I use the strSelectString variable instead of listing the matching strings out it doesn't select anything. When I do a Debug.Print of the string variable's contents it appears formatted the same as the explicit Case line. I tried it without the quotes in the text file but that didn't work either.

Any suggestions?

 

[Andrzejek]

Looks like VB.NET - if so, you are in wrong Forum.

Check:

[blue]
Debug.Print Trim(strRecordType)
Debug.Print strSelectString
[/blue]
Select Case Trim(strRecordType)            
    Case strSelectString

My guess - you have some extra " (double quotes) all over....

Have fun.

---- Andy

 

[jbradley]

Nope. It's VB6. I used File System Objects when I wrote this just to do something different.

I did another set of debug.print captures. Trim(strRecordType) is not quoted. In strSelectString the individual comma-separated elements are either quoted or not, as you would expect, depending on whether they are quoted or not in the control file. It doesn't make any difference one way or another, strRecordType is never matched.

On the other hand, if I replace strSelectString with the explicit line of quoted, comma-separated values then it works perfectly.

 

[Hypetia]

You cannot simply write VB code on the fly and use it. What you are trying to do will not work because the statement [tt]Case strSelectString[/tt] does not compare strRecordType with all string matches, but with one string only which literally looks like this: [tt]"String1", "String2", "String3", "String4", "String5"[/tt]

Try the following code which reads the file contents into an array and uses Filter function to see if a match is found.
___
[tt]
Dim tsControlFile As TextStream
Dim arrMatches() As String
Set tsControlFile = fspenTextFile(App.Path & "\ControlFile.txt", ForReading)
arrMatches = Split(tsControlFile.ReadAll, vbNewLine)
If UBound(Filter(arrMatches, """" & strRecordType & """", , vbTextCompare)) >= 0 Then
'match found
End If[/tt]
___

Here, contents of whole file are read at once with ReadAll method and parsed into an array (arrMatches) using the Split function. The Filter function searches this array for a match. If a match is found, it returns an array which has at least one element and UBound function returns a 0 or higher value. If no match is found, UBound returns -1, indicating an empty array with no match.

Note that this code assumes that you are using quotes around each match in the text file, as you stated above. If you remove those quotes, this code will not work.

 

[jbradley]

Thanks, Hypetia! That did the trick.

So if I understand correctly, the relation of the strSelectString variable to the explicit Case specification is loosely analagous to the relation of a scanned image file of a printed text document to the original text file. It is a representation that looks like the object being represented but it doesn't really have the proberties of the object?

In any case, thank you.

 

[strongm]

>Looks like VB.NET

Eh?

 

[dilettante]

When the list is variable or might be fairly long you might also consider a hash table approach.

If "text compares" are good enough you can use a Collection. Or the Scripting.Dictionary offers more flexibility.

Option Explicit
    
Private Coll As Collection
Private Dict As Scripting.Dictionary
    
Private Sub UpdateUI()
    With txtTry
        .SetFocus
        .SelStart = 0
        .SelLength = &H7FFF
    End With
End Sub
    
Private Sub cmdTryColl_Click()
    Dim Found As Boolean
    
    On Error Resume Next
    Found = Coll(txtTry.Text)
    On Error GoTo 0
    If Found Then
        lblResult.Caption = "Present"
    Else
        lblResult.Caption = "Missing"
    End If
         
    UpdateUI
End Sub
    
Private Sub cmdTryDict_Click()
    If Dict.Exists(txtTry.Text) Then
        lblResult.Caption = "Present"
    Else
        lblResult.Caption = "Missing"
    End If
    
    UpdateUI
End Sub
    
Private Sub Form_Load()
    Dim F As Integer
    Dim S As String
    
    Set Coll = New Collection 'Always "TextCompare" (case insensitive).
    Set Dict = New Scripting.Dictionary
    Dict.CompareMode = BinaryCompare 'Not an option with Collections.
    F = FreeFile(0)
    Open "strings.txt" For Input As #F
    Do Until EOF(F)
        Line Input #F, S
        Coll.Add True, S
        Dict.Add S, True
    Loop
    Close #F
End Sub

 

[Hypetia]

>loosely analagous

To understand the point, consider this simple code.
[tt]
MsgBox Time
[/tt]
It will show you the current time in a MsgBox.

Now see this.
[tt]
Message = "Time"
MsgBox Message
[/tt]
It will not do the same as above. Instead it will simply show a message box with the text Time (Same as [tt]MsgBox "Time"[/tt]).

Same thing goes with your Case statement due to which it fails to work.