Evaluate boolean value in where clause

[tuxalot]

I have tblPrefs to allow users to set preferences. Option group [tblPrefs].[ShowOnlyUnfinal ized] has three choices:

1 = unfinalized
2 = finalized
3 = both

I have a boolean value at [tblSrvRspns].[SurveyComplete], for finalized and unfinalized records. I am trying to query the table and I cannot figure out how to pull out both unfinalized and finalized records (option 3 above). If a users selects option 1 or 2 in tblPrefs the where clause below works correctly.

WHERE is not working for option 3:

WHERE (((tblSrvRspns.SurveyComplete)=
  IIf([tblPrefs].[ShowOnlyUnfinalized]=1,False)
    Or (tblSrvRspns.SurveyComplete)=
  IIf([tblPrefs].[ShowOnlyUnfinalized]=2,True)
    Or (tblSrvRspns.SurveyComplete)=
  IIf([tblPrefs].[ShowOnlyUnfinalized]=3,([tblSrvRspns].[SurveyComplete])=False
    Or ([tblSrvRspns].[SurveyComplete])=True)))

thanks for the help.

[tuxalot]

Figured it out. I added a fourth condition in the query builder:

WHERE
(((tblSrvRspns.SurveyComplete)=
IIf(tblPrefs.ShowOnlyUnfinalized=1,False)))
  Or (((tblSrvRspns.SurveyComplete)=
IIf(tblPrefs.ShowOnlyUnfinalized=2,True)))
  Or (((tblSrvRspns.SurveyComplete)=
IIf(tblPrefs.ShowOnlyUnfinalized=3,False)))
  Or (((tblSrvRspns.SurveyComplete)=
IIf(tblPrefs.ShowOnlyUnfinalized=3,True)));

[NeoPa]

Sometimes the posted code gives clues as to what the question means, but this time even the code is complicated beyond understanding.

I'm sure there is a much easier and neater way of solving your issue if only we had a clue as to what you were asking.

[Rabbit]

You can simplify by taking the boolean, taking the absolute value, adding 1, and then taking the bitwise AND of the values. If the result is larger than 1, you can return the row.

WHERE (ABS(booleanField) + 1) AND preferenceField > 0

[zmbd]

Why make it so rough?
Wow I hate nested IIF statments! :(

OK, because I didn't want to build a whole bunch of forms and tables... I added a boolean field to one of the test DB I have around from work here.
To that in the SQL I added a simple [AskMe] as a prompt in the select query as that is not a field name, MS Access prompts for a value.

SELECT people_pk, 
   people_FirstName, 
   People_LastName, 
   people_email, 
   tblSrvRspns.SurveyComplet
FROM tblSrvRspns
WHERE (
   SWITCH(
      [AskMe]=1, [SurveyComplet]=0,
      [AskMe]=2, [SurveyComplet]=-1,
      [AskMe]=3,([SurveyComplet]=0 
           OR [SurveyComplet]=-1)
    )
);

Now with the proper forms and so forth the [AskMe] could be replaced with the [tblprefs]![ShowOnlyUnfinis hed] value.

Yes it works. :)