What am I doing wrong with Iif

[Guest]

This is the SQL from my Query. The option group is on a form to select active
or inactive staff. Staff status may be "Staff","Emergency",or"Inactive".

SELECT [Staff Name].FirstName, [Staff Name].LastName, [Staff Name].Status
FROM [Staff Name]
WHERE ((([Staff
Name].Status)=IIf([Forms]![Staff]![optiongroup]=2,"Inactive",Not ([Staff
Name].Status)="Inactive")));

It works when [optiongroup]=2 but not otherwise. How do I fix the falsepart?

 

[Graham R Seach]

I'm not sure what you're trying to accomplish here.

What range of values can be in [Status]? What are the range of values (and
their corresponding textual representations) possible in
Forms!Staff!optiongroup?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Jerry Porter]

So if the user selects "Inactive" (2), you want to display Inactive
records. If they select either "Staff" (0) or "Emergency (1)", you want
all the active stave (0 or 1). Right?

If the user selects 0 or 1, then what comes out of the IIF is:
Not ([Staff Name].Status)="Inactive"

And the resulting criteria is:
Where [Staff Name].Status = Not ([Staff Name].Status)="Inactive"
which is not what you want.

You can't use "Status = " if you want to return more than one value (0
or 1 in this case).

Try this:
Where (Forms]![Sta­ff]![optiongroup]=2 AND [Staff Name].Status = 2) OR
(Forms]![Sta­ff]![optiongroup] <> 2 AND [Staff Name].Status <> 2)

Another that might work and might be clearer:
WHERE ((([Staff Name].Status) Between
IIF(Forms]![Sta­ff]![optiongroup] = 2,2,0) AND
IIF(Forms]![Sta­ff]![optiongroup] = 2,2,1)
If they choose 2, you get values between 2 and 2, otherwise between 0
and 1.

Jerry