VBA functional equivalent of "IN" or "AMONG"?

[(Pete Cresswell)]

I'd like to avoid coding a long set of IF... OR IF...statements.

I can do a Select Case, but that would only let me case out on
a single value.

What I'm looking for is something like

If thisVal OR thatVal AMONG("X", "Y", "Z")....

The main thing is getting the allowed values in one place where the next guy can
see/update them easily.

Somehow Enum comes to mind...
--
PeteCresswell

[(Pete Cresswell)]

RE/

I'd like to avoid coding a long set of IF... OR IF...statements.

The actual statements are:
------------------------------------------------
If !Val_MSD = "COP" _
Or !Val_MSD = "GO" _
Or !Val_MSD = "ETM" _
Or !Val_MSD = "INS" _
Or !Val_MSD = "MISC" _
Or !Val_MSD = "PRE-RE" _
Or !Val_MSD = "REV" _
Or !Val_MLD = "COP" _

Or !Val_MLD = "GO" _
Or !Val_MLD = "ETM" _
Or !Val_MLD = "INS" _
Or !Val_MLD = "MISC" _
Or !Val_MLD = "PRE-RE" _
Or !Val_MLD = "REV" Then
theReason = "When FIMA2 value='BOND' and Fimma-1 value among
('COP', 'GO', 'ETM', 'INS', 'MISC', 'PRE-RE', 'REV') difference is OK."
------------------------------------------------

--
PeteCresswell

[rkc]

(Pete Cresswell) wrote:

I'd like to avoid coding a long set of IF... OR IF...statements.

I can do a Select Case, but that would only let me case out on
a single value.

What I'm looking for is something like

If thisVal OR thatVal AMONG("X", "Y", "Z")....

Case "X", "Y", "Z" is a valid statement in a Select Case block.

[Salad]

"(Pete Cresswell)" wrote:

I'd like to avoid coding a long set of IF... OR IF...statements.

I can do a Select Case, but that would only let me case out on
a single value.

What I'm looking for is something like

If thisVal OR thatVal AMONG("X", "Y", "Z")....

The main thing is getting the allowed values in one place where the next guy can
see/update them easily.

Somehow Enum comes to mind...
--
PeteCresswell

Sometimes I may do something like this
Dim s as string
Dim sSearch as string
s = "*Mike*Sam*Joe*"
sSearch = "Joe"
If instr(s,"*" & strSearch & "*") > 0 then...

I'm using 97 tho.

[Bas Cost Budde]

Nice one, will keep that in memory! Thanks.

Salad wrote:

s = "*Mike*Sam*Joe*"
If instr(s,"*" & strSearch & "*") > 0 then...
I'm using 97 tho. So? Does InStr get deprecated somewhere?

--
Bas Cost Budde

[Bob Quintal]

"(Pete Cresswell)" <x@y.z> wrote in
news:ik********************************@4ax.com:

RE/

I'd like to avoid coding a long set of IF... OR
IF...statements.

The actual statements are:
------------------------------------------------
If !Val_MSD = "COP" _
Or !Val_MSD = "GO" _
Or !Val_MSD = "ETM" _
Or !Val_MSD = "INS" _
Or !Val_MSD = "MISC" _
Or !Val_MSD = "PRE-RE" _
Or !Val_MSD = "REV" _
Or !Val_MLD = "COP" _

Or !Val_MLD = "GO" _
Or !Val_MLD = "ETM" _
Or !Val_MLD = "INS" _
Or !Val_MLD = "MISC" _
Or !Val_MLD = "PRE-RE" _
Or !Val_MLD = "REV" Then
theReason = "When FIMA2 value='BOND' and Fimma-1
value among
('COP', 'GO', 'ETM', 'INS', 'MISC', 'PRE-RE', 'REV')
difference is OK."
------------------------------------------------

How about
if isInlist(!val_msd) or isinlist(!val_mld) then ...

Public Function isInList(stFind As String) As Boolean
Dim stItems As String
stItems = "cop,go,etm,ins,misc,pre-re,rev"
isInList = InStr(1, stItems, stFind) > 0
End Function

Bob Q

[Salad]

Bob Quintal wrote:

How about
if isInlist(!val_msd) or isinlist(!val_mld) then ...

Public Function isInList(stFind As String) As Boolean
Dim stItems As String
stItems = "cop,go,etm,ins,misc,pre-re,rev"
isInList = InStr(1, stItems, stFind) > 0
End Function

Bob Q

A nice function, short and sweet.

You might need to adjust that for numbers tho. Ex: you pass the number
1 and the string contains an 11. And you need to consider strings too..
Ex: you are searching for Bob and the string contains "Bobbie". That's
why I put a delimiter around each item in the string and the search
for...I use an * but any character would work.

[(Pete Cresswell)]

RE/

if isInlist(!val_msd) or isinlist(!val_mld) then ...

Public Function isInList(stFind As String) As Boolean
Dim stItems As String
stItems = "cop,go,etm,ins,misc,pre-re,rev"
isInList = InStr(1, stItems, stFind) > 0
End Function

That's the kind of functionality I was looking for.

Thanks!
--
PeteCresswell

[Trevor Best]

On Sun, 01 Feb 2004 01:21:15 GMT in comp.databases.ms-access, "(Pete
Cresswell)" <x@y.z> wrote:

I'd like to avoid coding a long set of IF... OR IF...statements.

I can do a Select Case, but that would only let me case out on
a single value.

What I'm looking for is something like

If thisVal OR thatVal AMONG("X", "Y", "Z")....

The main thing is getting the allowed values in one place where the next guy can
see/update them easily.

Somehow Enum comes to mind...

An alternative to Bob's excellent function is the use of separate
parameters in an array...

Function Among(pvarWhat, ParamArray pvarAr()) As Boolean
Dim i As Long
For i = LBound(pvarAr) To UBound(pvarAr)
If pvarWhat = pvarAr(i) Then
Among = True
Exit For
End If
Next
End Function
--
A)bort, R)etry, I)nfluence with large hammer.

[(Pete Cresswell)]

RE/

Function Among(pvarWhat, ParamArray pvarAr()) As Boolean
Dim i As Long
For i = LBound(pvarAr) To UBound(pvarAr)
If pvarWhat = pvarAr(i) Then
Among = True
Exit For
End If
Next
End Function

I like that even better - just because it reads better when coded in the calling
routine.
--
PeteCresswell

[rkc]

"(Pete Cresswell)" <x@y.z> wrote in message
news:83********************************@4ax.com...

RE/

Function Among(pvarWhat, ParamArray pvarAr()) As Boolean
Dim i As Long
For i = LBound(pvarAr) To UBound(pvarAr)
If pvarWhat = pvarAr(i) Then
Among = True
Exit For
End If
Next
End Function
I like that even better - just because it reads better when coded in the

calling routine.

Why not use a table for your static values and a query to see if the
variable
exists in the table? Wasn't management of the static values one of the
reasons
for the question?

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Have any of you thought of using the Eval() function? (debug window):

? eval("2 in (2, 3)")
- -1

Eval() returns true if 2 is in the list (2, 3); false if 2 is not in
the list.

Rgds,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQCB6oYechKqOuFEgEQK6ZQCgxM99LSJTkZ2J8yK9PytZWR qmO2EAniKi
ZqzZunNsiRdP42ghLJ2t8tot
=AqrY
-----END PGP SIGNATURE-----
(Pete Cresswell) wrote:

RE/

Function Among(pvarWhat, ParamArray pvarAr()) As Boolean
Dim i As Long
For i = LBound(pvarAr) To UBound(pvarAr)
If pvarWhat = pvarAr(i) Then
Among = True
Exit For
End If
Next
End Function

I like that even better - just because it reads better when coded in the calling
routine.