IN Operator in Module

  • Thread starter Thread starter AlCamp
  • Start date Start date
A

AlCamp

Can't the IN operator be used in a form module with an IF.. THEN
statement? Help shows an example in an SQL statement and an IFF statement.
Left(StyleID,1) yields a single letter string value...

If Left(StyleID, 1) In ("F","M","N","T") Then
'do something...
End If

Get error msg "GoTo or Then Expected"

Tried the syntax every which way but loose...

Thanks in advance,
Al Camp
 
Duane,
So... I take it IN can't be used in an IF ... THEN. That's what I
figured...

Your solution ia a good example of thinking outside the box!!
That'll do just fine.

Thanks,
Al Camp
 
Hi Al,

You're right but (I think) for the wrong reason.

There's nothing special about If...Then that prevents you using In; it's
just that VBA doesn't have an In operator.

Perhaps you're thinking of SQL, which has an IN keyword that works like
an operator
WHERE Name IN ("Smith","Jones")
and can be used in a criterion cell in the Access query designer.

The closest VBA gets is the For Each ... In ... construct, which
iterates the members of an array or collection.
 
You can use the IN operator in the expression evaluator:

s = "('F','M','N','T')"
s = Left(StyleID,1) & " IN " & s
If eval(s) then

But most people would be more comfortable with a Case statement.

In fact, the VB case statement is so flexible, (you can use
functions as case labels) that the only situation I can imagine
justifying the use of IN, is with programmers who are accustomed
to the more limited Case statement provided by other languages.

In some languages, you aren't able to use variables as case
labels, so when you need a dynamic case statement you use
constructs equivalent to IN, with a string variable (as shown
above) containing all the case labels.

BTW, this is an example of the subtle differences between EVAL,
SQL, and the Immediate Window. 'IN' works in SQL and in EVAL,
but doesn't work in the Immediate Window.

(david)
 
You can use the IN operator in the expression evaluator:

s = "('F','M','N','T')"
s = Left(StyleID,1) & " IN " & s
If eval(s) then

But most people would be more comfortable with a Case statement.

In fact, the VB case statement is so flexible, (you can use
functions as case labels) that the only situation I can imagine
justifying the use of IN, is with programmers who are accustomed
to the more limited Case statement provided by other languages.

In some languages, you aren't able to use variables as case
labels, so when you need a dynamic case statement you use
constructs equivalent to IN, with a string variable (as shown
above) containing all the case labels.

BTW, this is an example of the subtle differences between EVAL,
SQL, and the Immediate Window. 'IN' works in SQL and in EVAL,
but doesn't work in the Immediate Window.

(david)
 
Thanks for that, David. I'm ignorant of Eval() because it doesn't work
like the eval I'm used to<g>.



You can use the IN operator in the expression evaluator:

s = "('F','M','N','T')"
s = Left(StyleID,1) & " IN " & s
If eval(s) then

But most people would be more comfortable with a Case statement.

In fact, the VB case statement is so flexible, (you can use
functions as case labels) that the only situation I can imagine
justifying the use of IN, is with programmers who are accustomed
to the more limited Case statement provided by other languages.

In some languages, you aren't able to use variables as case
labels, so when you need a dynamic case statement you use
constructs equivalent to IN, with a string variable (as shown
above) containing all the case labels.

BTW, this is an example of the subtle differences between EVAL,
SQL, and the Immediate Window. 'IN' works in SQL and in EVAL,
but doesn't work in the Immediate Window.

(david)
Click to expand...
 
Back
Top