Combining where and IIF statements in Access

Question

I have a form with three buttons relating to three yes/no variables that are not mutually exclusive in a subform. By default Access sets up the relationship using "and" so if I select two of the three buttons it shows me all the observations that have "yes" for both, but I want the buttons to have an "or" relationship so that if I select two buttons I want all the observations where it says yes to either of the two checked variables.

I'm trying to write a query that will do this, but Access doesn't support SQL case function and I can't get it to work with the IIF statement.

I want to code something along the lines of:

select var1,var2,var3 
from table 
where iif(form!form1!var1=yes and form!form1!var2=no and form!form1!var3=yes,table.var1=yes or table.var3=yes and table.var2=no,...)

It seems you can only use iif in certain places. Any ideas how to do this?

You can't access form elements directly in a query. You'll have to use a prepared query or concatenate. — serakfalcon
@serakfalcon, it's not true. MS Access queries can get values from opened forms. See: support.microsoft.com/kb/304428?wa=wsignin1.0 — Maciej Los
@MaciejLos I stand corrected. For some reason I couldn't get it to work in adodb querydef objects but maybe that's something else entirely... — serakfalcon
I can definitely access form elements because I've already gotten that part to work. The question is how do I access the elements with "or" rather than "and" — Carl
An IIf statement has three parts: expression, truepart, falsepart. A comma separates each part, so if you need various Ands and Ors, use brackets, eg And (table.var1=yes or table.var3=yes). Make another stab at it and then post the whole statement. — Fionnuala

Wayne G. Dunn Wayne G. Dunn · Accepted Answer · 2015-02-24T02:38:03

The SQL shown below will return the proper rows if the following steps are taken:

SELECT Table1.Var1, Table1.Var2, Table1.Var3, Table1.ID
FROM Table1
WHERE ((([Forms]![frmChildTable]![bVar1] And [var1])=True)) 
    OR ((([Forms]![frmChildTable]![bVar2] And [var2])=True)) 
    OR ((([Forms]![frmChildTable]![bVar3] And [var3])=True));

Create 3 textboxes on the form named bVar1, bVar2, bVar3
Set the format to Yes/No
Have your existing three buttons toggle the controls

I assume your use of the word 'button' meant 'Command Button'. If you wanted to use 'Option Buttons', then no code necessary to toggle.

Combining where and IIF statements in Access

1 Answers