IN operator bombing in VBA

[rpochoda]

Whenever I try to use IN as a condition in VBA I get an compile error, either "Expected list separator or )" or "Expected Then or Go To", with the IN highlighted.

For example, I tried both

If Me![Status] in (1,3,5) Then
If iif(Me![Status] in (1,3,5),-1,0) Then

where Status is a control on the current form bound to an integer field.

Both conditions work in a query.

Any ideas? Thanks

 

[PHV]

In is a SQL operator, not a VBA one.
A workaround:
If InStr(",1,3,5,", "," & Me!Status & ",") Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Remou]

I don't think you can do that. How about:
If InStr("1,2,3",Me.Status)>0 Then ...

 

[rpochoda]

Thanks guys, I'll give it a try. (I've already used OR's as a work around, which may be better for self-documentation and perhaps a bit faster since it keeps everything numeric.)

 

[PHV]

Another way:
If Eval(Me!Status & " In (1,3,5)") Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

HI\i,

or...

Select Case Me![Status]
  Case 1,3,5
    'here's what to do
  Case Else
    'otherwise...
End Select

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}