Using IIF and LIKE in the same query criteria

[RandDUser]

In a form, I have an unbound textbox (TEXT1) with an option button next to it (OPTION1). In a query, I have the field, LAST NAME. The criteria for LAST NAME is linked to TEXT1 in the form:

[Forms]![frm_MAIN]![TEXT1]

But, if the person highlights the option button, I want the criteria to acknowledge that as a partial search:

Like "*" & [Forms]![frm_MAIN]![TEXT1] & "*"

So I wrote my criteria as such:

IIF([Forms]![frm_MAIN]![OPTION1].Value = -1, Like "*" & [Forms]![frm_MAIN]![TEXT1] & "*", [Forms]![frm_MAIN]![TEXT1])

If I leave the option button unchecked, it works, but if it's checked (-1), it returns zero records. It should at least return the same amount of records. Any suggestions?

Thank you!

 

[dhookom]

You can't place the "Like" inside the expression. I think this is what you want:

Like IIF([Forms]![frm_MAIN]![OPTION1].Value = -1, "*" & [Forms]![frm_MAIN]![TEXT1] & "*", "*")

or maybe

Like "*" & [Forms]![frm_MAIN]![TEXT1] & "*" Or [Forms]![frm_MAIN]![OPTION1].Value = 0

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[PHV]

Like IIf([Forms]![frm_MAIN]![OPTION1] = True, '*' & [Forms]![frm_MAIN]![TEXT1] & '*', [Forms]![frm_MAIN]![TEXT1])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Thanks PH. After reading your response, I think I misunderstood the question.

I think the generic control names of "TEXT1" and "OPTION1" threw me for a loop.

Duane MS Access MVP
Now help me support United Cerebral Palsy