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