Problems evaluating Variable in List in a Do While statement

[tdfreeman]

I am trying to code the following:

Do While (Mid(s, 5, 1) = "." or _
Left(s,4) in _
("9001", "9003", "9005", "9006", "9007", "9008&quot) _
And ts.AtEndOfStream <> True

The code errors on the in statement saying &quot;Expected: )&quot;. Obviously the &quot;in&quot; keyword is not a keyword in VBA like it is in SQL.

Any ideas. I don't want to have to separate each comparison out as it makes it hard to read.

Thanks,

Tammy
Thank you for your help.

Tammy

 

[beetee]

VB has the standard relational operators, the select case statement, the 'like' operator, but I'm not aware of a 'in' function. Of course you could always write your own:

Function MyIn(CompareTo As String, ParamArray Values())
Dim counter As Integer
For counter = LBound(Values) To UBound(Values)
If CompareTo = Values(counter) Then
MyIn = True
Exit Function
End If
Next counter
MyIn = False
End Function

Sub testIn()
Dim MyValue As String
MyValue = &quot;005&quot;
Debug.Print MyIn(MyValue, &quot;001&quot;, &quot;002&quot;, &quot;003&quot;, &quot;004&quot
End Sub

 

[JeremyNYC]

Tammy,

Did you build the data source for this or are these data being handed to you by someone else. The best solution, if it's a database over which you have control, would be to break that field up into several fields, so you don't have to strip it apart when you want to use it. It's much easier to concatenate things for display and printing.

If you're stuck with the data, though, you'll have to deal.

The &quot;in&quot; operator in SQL only works for numeric values, I believe, so even if VBA had one, it wouldn't work for this.

You could certainly just use a bunch of ORs to do this.

Is there a way that you could make the sql that builds the recordset do some of this work?

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[tdfreeman]

I am a little confused with the responses, I am looking to write a simple Do While statement. My plan was to write this:

Do While (Mid(s, 5, 1) = &quot;.&quot; or _
Left(s,4) in _
(&quot;9001&quot;, &quot;9003&quot;, &quot;9005&quot;, &quot;9006&quot;, &quot;9007&quot;, &quot;9008&quot) _
And ts.AtEndOfStream <> True

However, it doesn't work. The only solution I am aware of is this:

Do While (Mid(s, 5, 1) = &quot;.&quot; or _
Left(s,4) = &quot;9001&quot; or Left(s,4) = &quot;9003&quot; or _
Left(s,4) = &quot;9005&quot; or Left(s,4) = &quot;9006&quot; or _
Left(s,4) = &quot;9007&quot; or Left(s,4) = &quot;9008&quot _
And ts.AtEndOfStream <> True

I have never taken a Visual Basic Class so I learn through the help menus and what I find on the internet. Of course, I want to know the easiest way to write code, so I am asking for a way to write this easier.

Thanks,

Tammy Thank you for your help.

Tammy

 

[beetee]

Function MyIn(CompareTo As String, ParamArray Values())
Dim counter As Integer
For counter = LBound(Values) To UBound(Values)
If CompareTo = Values(counter) Then
MyIn = True
Exit Function
End If
Next counter
MyIn = False
End Function

...

Do While ((Mid(s, 5, 1) = &quot;.&quot; or MyIn(Left(s, 4), _
&quot;9001&quot;, &quot;9003&quot;, &quot;9005&quot;, &quot;9006&quot;, &quot;9007&quot;, &quot;9008&quot) _
And ts.AtEndOfStream <> True

I took the liberty of adding what appear to be a missing pair of parenthesis.