Using "IIf" or "In" in VBA

[Jim Pockmire]

I believe there is a way to use these functions in VBA, but I ca't remember
how to do it.

 

[Larry Linson]

I believe there is a way to use these functions in VBA, but I ca't remember
how to do it.

This will work,

somevar = IIF(avar = something, retvalfortrue, retvalforfalse)

but most think it easier to follow, later, if you use

If avar = something Then
somevar = retvalfortrue
Else
somevar = retvalforfalse
End If

or the alternative

IF avar = something Then somevar = retvalfortrue Else somevar =
retvalforfalse

FOR EACH varname IN arrayorgroup

is one valid use of IN in VBA. (Open the code window, type "In" in a module,
put the cursor on it, press F1, and that's the Help topic.)

Larry Linson
Microsoft Access MVP

 

[Dirk Goldgar]

I believe there is a way to use these functions in VBA, but I ca't
remember how to do it.

By "In", are you referring to the SQL In operator, as "WHERE fieldname
In (1, 2, 3, 4)"? AFAIK, that operator doesn't exist in VBA, though
there are several ways to do the equivalent. One is the Select Case
statement; e.g.,

Select Case expression
Case 1, 2, 3, 4
' ... do something ...
Case 5, 6, 7, 8
' ... do something else ...
Case Else
' ... do the default thing ...
End Select

 

[Marshall Barton]

I believe there is a way to use these functions in VBA, but I ca't remember
how to do it.

There is an IIf function in VBA and a very similar, but not
quite identical, function in SQL. Help explains the VBA
function fairly well.

In is an SQL operator, but it is not avaliable in VBA.
However you can use it in the Eval function.

y = Eval( x & "IN (1,2,3,4)" )