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.

 

[KPR]

Hi Duane,

This is a record source for a Report. The CatID was just an example. In
reality these are StateID's and I need to split my report by Regions, where
Region1 contains 1 State and Region2 contains 2 States.

Thanks,
Ken

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?

--
Duane Hookom
Microsoft Access MVP

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

 

[KPR]

Hi Fred,

In reality these are StateID's where I need to split my report by Region,
where Region1 contains 1 State and Region2 contains 2 States.

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.

 

[John Vinson]

Hi Fred,

In reality these are StateID's where I need to split my report by Region,
where Region1 contains 1 State and Region2 contains 2 States.

I'd suggest a different approach: create a table (or adapt your States
table) with a field for Region and State, identifying which region each state
belongs to. Join this table to your query by State and avoid using any IIF at
all.

 

[Duane Hookom]

I would agree with John Vinson. Also, I try not to put dynamic criteria in
the report's record source query. I prefer to use the Where Condition of the
DoCmd.OpenReport method.

Dim strWhere as String
strWhere = "1=1 "
If Me.my_TickBox = True Then
strWhere = strWhere & " And {MyField} = {some condition} "
Else
strWhere = strWhere & " And {MyField} = {some other condition} "
End If
DoCmd.OpenReport "rptMyReport", acPreview, , strWhere

--
Duane Hookom
Microsoft Access MVP

Hi Duane,

This is a record source for a Report. The CatID was just an example. In
reality these are StateID's and I need to split my report by Regions, where
Region1 contains 1 State and Region2 contains 2 States.

Thanks,
Ken

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?

--
Duane Hookom
Microsoft Access MVP

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