Embedded 'Or' Criteria in an IIF Statement

[KPR]

Hi,

I can't seem to find the right syntax for my IIf statement that I have in a
Query Parameter. I have a [frm_MyForm] with a tickbox. If the tickbox isn't
checked I want to pull records where a CatID = 1. If the tickbox is ticked I
want to pull records where CatID = 2 Or 3. Here is my Criteria Statement in
the [CatID] column in my query that I can't get to work...

IIf([Forms]![frm_MyForm]![my_Tickbox]=-1,([tbl_MyTable].[CatID])=2 Or
([tbl_MyTable].[CatID])=3,1)

I can't get any records returned if the tickbox is checked, if the tickbox
isn't checked I'm returning the proper records.

Thanks,
Ken

 

[Brian]

You have, in one part of your IIF, field names, and in the other, just the
value. You can try something like this (I have not tested it, but it should
get you closer):

IIf([Forms]![frm_MyForm]![my_Tickbox]=-1,>1,1)

or

IIf([Forms]![frm_MyForm]![my_Tickbox]=-1,Between 2 And 3,1)

 

[Duane Hookom]

As you have found your expression with an operator (=) inside the IIf() won't
work. Neither will the suggestion from Brian which also has an operator issue.

Is this query the record source for a report or form?
Are there other numbers for CatID or just 1,2, & 3?

 

[fredg]

Hi,

I can't seem to find the right syntax for my IIf statement that I have in a
Query Parameter. I have a [frm_MyForm] with a tickbox. If the tickbox isn't
checked I want to pull records where a CatID = 1. If the tickbox is ticked I
want to pull records where CatID = 2 Or 3. Here is my Criteria Statement in
the [CatID] column in my query that I can't get to work...

IIf([Forms]![frm_MyForm]![my_Tickbox]=-1,([tbl_MyTable].[CatID])=2 Or
([tbl_MyTable].[CatID])=3,1)

I can't get any records returned if the tickbox is checked, if the tickbox
isn't checked I'm returning the proper records.

Thanks,
Ken

Are the ONLY choices 1, 2, or 3?

Here is the query's SQL Where clause:

WHERE YourTable.[CatID] = IIf([Forms]![frm_MyForm]![my_Tickbox] =0,1)
OR YourTable.[CatID] = IIf([Forms]![frm_MyForm]![my_Tickbox] =-1,2) OR
YourTable.[CatID] = IIf([Forms]![frm_MyForm]![my_Tickbox] =-1,3);

Change YourTable to whatever the actual table name is.