INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...I have been a grateful member of this site for several years. I love this site and refer everyone to it!..."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > Programmers > Languages > Visual Basic(Microsoft): Version 5 & 6 Forum

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

CODE

If String1 = String2 Or _
If String1 = String3 . . . Then

by doing something like a SQL query

CODE

IF String1 IN (String2, String3 . . .)

judgeh59 (IS/IT--Management)

19 Apr 12 18:23

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 (Programmer)

19 Apr 12 21:07

Or...:

CODE

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

Have fun.

---- Andy

SkipVought (Programmer)

19 Apr 12 22:49

sorry, the else SHOULD be ....

'close but no cigar

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

jbradley (Programmer)

20 Apr 12 8:32

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 (Programmer)

20 Apr 12 13:24

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:

CODE

Case "String1", "String2", "String3", "String4", "String5"

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

CODE

"String1"
"String2"
"String3"
"String4"
"String5"

I read the file into a string variable:

CODE

    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

Then I do my Select clause:

CODE

Select Case Trim(strRecordType)
Case strSelectString

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 (Programmer)

20 Apr 12 14:37

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

Check:

CODE

Debug.Print Trim(strRecordType)
Debug.Print strSelectString

Select Case Trim(strRecordType)
Case strSelectString

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

Have fun.

---- Andy

jbradley (Programmer)

20 Apr 12 15:26

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 (Programmer)

20 Apr 12 15:36

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

Try the following code which reads the file contents into an array and uses Filter function to see if a match is found.
___

Dim tsControlFile As TextStream
Dim arrMatches() As String
Set tsControlFile = fso.OpenTextFile(App.Path & "\ControlFile.txt", ForReading)
arrMatches = Split(tsControlFile.ReadAll, vbNewLine)
If UBound(Filter(arrMatches, """" & strRecordType & """", , vbTextCompare)) >= 0 Then
'match found
End If
___

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 (Programmer)

20 Apr 12 17:00

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 (MIS)

20 Apr 12 18:21

>Looks like VB.NET

Eh?

dilettante (MIS)

20 Apr 12 20:24

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.

CODE

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 (Programmer)

21 Apr 12 5:37

>loosely analagous

To understand the point, consider this simple code.

MsgBox Time

It will show you the current time in a MsgBox.

Now see this.

Message = "Time"
MsgBox Message

It will not do the same as above. Instead it will simply show a message box with the text Time (Same as MsgBox "Time").

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

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Visual Basic(Microsoft): Version 5 & 6

Member Login

Come Join Us!

LINK TO THIS FORUM!

Tek-Tips LinkedIn® Group

Partner With Us!

Feedback

Geography

Tek-Tips Shirts!

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

CODE

CODE

CODE

CODE

CODE

CODE

CODE

CODE

CODE

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Back To Forum

Join Tek-Tips® Today!

Tek-Tips LinkedIn^® Group

Join Tek-Tips^® Today!