Using the IN operator with IIF in a query criteria

[gswan]

I am trying to select specific records based on input in a query. This criteria currently works to select everything with an ID less than 21 or 31 based on the users entry:
<IIf([Enter "Y" for List 1]="Y",21,31)

I now need to change the query so that only certain records are selected based on the users entry. As an example, I would like to select 1, 3, 5 and 7 if they enter “Y”. And I would like to select 2, 4, 6 and 8 if they don’t enter a “Y”.

Here is what I have tried:

In IIf([Enter "Y" for List 1]="Y",(3,5,7,9),(2,4,6,8))

With this criteria, I get this error – “The In operator you entered requires parentheses”. I have tried many other variations with no luck. Anyone have an idea of how I can do this?

 

[nanscombe]

In the query designer I would use two columns.

Field: yourRowField | List1: UCase(Nz([Enter "Y" for List 1],"N")
Criteria: In (3,5,7,9) | "Y"
Or: In (2,4,6,8) | "N"

This is effectively saying (In(3,5,7,9) AND "Y") OR (In(2,4,6,8) AND "N") where "N" is given as the default value for leaving the List1 prompt empty.

 

[gswan]

Can you give more details? I don't understand what to do from what you've shown.

 

[gswan]

Here's what I've done, and it doesn't work, so I'm obviously not doing what you're suggesting -

Column 1-
Field: PositionID
Criteria: LCase([Enter Y or N])

Column 2
Field: PositionID
Criteria: (In (3,5,7,9) And "Y")
Or: (In (2,4,6,8) And "N")

The error message is - This expression is typed incorrectly, or it is too complex to be evaluated.

 

[nanscombe]

Column 1 -
Field: PositionID
Criteria: In (3,5,7,9)
Or: In (2,4,6,8)

Column2 -
Field: List1: UCase(Nz([Enter "Y" for List 1],"N")
Criteria: "Y"
Or: "N"

 

[gswan]

That helped! Thanks for the info, it is now working!