IIF query using OR operators not working

Question

I'm trying to run the following as criteria in an MS access query. Basically what I want to do is:

• If checkbox = True then all records, including those with blank or Null fields are shown (the default value in my form's combo box [combo9] is "*")
• If checkbox = False then only records that match the value in Combo9 are shown

My current expression doesn't give any errors, but also doesn't produce any results! The TRUE and FALSE halves of the expression work fine on their own, but don't work when combined into the iif expression.

Like IIf([Forms]![F_leg_reg]![Check25]=True,Like [Forms]![F_leg_reg]![Combo9] Or "" Or Is Null,Like [Forms]![F_leg_reg]![Combo9])

Can someone please tell me what I'm doing wrong here? Thanks in advance.

Answer 1

Not sure you need so many likes, you are using the or logical operator in the return value which doesn't make sense

IIF (condition, value-if-true, value-if-false)

so..

Like IIF ([forms]![foo].[text1] = "1" OR [forms]![foo].[text2] = "1", 'bar', 'foobar')

if either conditions are met IIF will return 'bar' else it will return 'foobar'

you can nest it if you want so

Like IIF ([forms]![foo].[text1] = "1" OR [forms]![foo].[text2] = "1", 'bar', 
        IIF ([forms]![foo].[text1] = "2" AND [forms]![foo].[text2] = "2", 'foobar', null)
    )

if either conditions are met IIF will return 'bar' else we check to see if text1 and text2 are "2" if so return 'foobar' else we return null

hope this helps

Answer 2

You're repeating the keyword, Like, within the IIf() expression. That won't work. You could fix the IIf(), but I would use a different approach ... which fits my brain better.

This will returns all rows when the checkbox is checked, but no rows when unchecked.

SELECT a_field, another_field, field_2_search 
FROM YourTable
WHERE [Forms]![F_leg_reg]![Check25]=True;

When the checkbox is unchecked, limit the rows to those where the field_2_search values contain the combo's value.

SELECT a_field, another_field, field_2_search
FROM YourTable
WHERE
    [Forms]![F_leg_reg]![Check25]=False
    AND field_2_search Like "*" & [Forms]![F_leg_reg]![Combo9] & "*";

But you want only one query, not two, so combine those WHERE clauses with an OR.

SELECT a_field, another_field, field_2_search 
FROM YourTable
WHERE
    [Forms]![F_leg_reg]![Check25]=True
    OR (
    [Forms]![F_leg_reg]![Check25]=False
    AND field_2_search Like "*" & [Forms]![F_leg_reg]![Combo9] & "*"
    );