IIf with Odd + Even Numbers

[shaggy]

I am trying to select records based on weather or not a field is odd or even.

If the [Month] is odd the [CYCLE] s/b <=21
If the [Month] is even the [CYCLE] s/b >=22

IIf([Month] Mod 2=0,>"21",<"22") This doesn't return any records.

IIf([Month] Mod 2=0,>21,<22) This gives me any error "expression typed incorrectly or to complex to be evaluated".

IIf([Month] Mod 2=0,"22","01") This works, but only give me only one value from the [CYCLE] field.

I've tested the Mod statement seperately and it seem to be working. 0 for even and 1 for odd.

I've tried everything I can think of but nothing seems to work.

Any help would be appreciated.

 

[Jon K]

Assuming [Cycle] is a numeric field in the table, try this setting in a new column in the query grid:-

Field: iif([Month] Mod 2=0, [Cycle]>=22, [Cycle]<=21)
Show: uncheck
Criteria: <>False


<>False is one of Access's defaults. It tells Access the IIf expression in the Field: cell is a criteria. If you directly type the IIf expression in the Where Clause of the SQL statement in SQL View, you don't need to type <>False.