>So, I tried
>
>    IIF( (MYFIELD in (1,3,4,5,7,8,9,11,12,13)), 0, 1 )
>
>for the index and
>
>    IIF( (MYFIELD in (1,3,4,5,7,8,9,11,12,13)), 0, 1 ) = 0
>
>in the WHERE clause ...
>

What if you try:

    IIF( (MYFIELD in (1,3,4,5,7,8,9,11,12,13)), MYFIELD, -MYFIELD)

and

    IIF( (MYFIELD in (1,3,4,5,7,8,9,11,12,13)), MYFIELD, -MYFIELD) > 0

Does it still avoid the index? Do you get the same if you use a calculated field rather than expression index (of course, then the query itself changes to MyCalculatedField > 0 rather than IN) or isn’t it a viable option to add a calculated field?

Note, I’ve never used expression indexes myself, so I’ve no clue about the answer…

Set