Filter query using checkbox and IIf function

[Jonathan Bickett]

Hi everyone.

I am trying to filter my query using a checkbox on a form. In the
criteria of a binary field on my query, I am using an IIf statement.
If the box is checked, I want it to return only 0's from the field. If
its not checked, I want it to return both 0's and 1's. It's easy to
make it return either 0's OR 1's, but I can't figure out how to make it
do both.

This is my current criteria:

IIf([Forms]![RIForm]![cbAcq]=True,0,1)

This obviously will return all values that are 0 when the box is
checked. When its not checked, it will return only values that are 1.

I tried dropping down to the next line of my criteria and adding a 0 to
the Or because I'm always going to want either 0's or both 1's and 0's.
However, this still did not filter it correctly.

Any ideas on how I could append my IIf statement to grab both 0's and
1's?

Thanks!

 

[John Spencer]

Try

IIf([Forms]![RIForm]![cbAcq]=True,0,1) Or [Forms]![RIForm]![cbAcq]=False

Or if the field always contains a value, you can check to see if it is equal
to itself

IIf([Forms]![RIForm]![cbAcq]=True,0, [TableName].[FieldName])

Return only 0 if checked
Return fields where the field is equal to itself if not checked.

 

[Jonathan Bickett]

John,

You are a genius. Both options worked great. I really appreciate it.

Jonathan

Try

IIf([Forms]![RIForm]![cbAcq]=True,0,1) Or [Forms]![RIForm]![cbAcq]=False

Or if the field always contains a value, you can check to see if it is equal
to itself

IIf([Forms]![RIForm]![cbAcq]=True,0, [TableName].[FieldName])

Return only 0 if checked
Return fields where the field is equal to itself if not checked.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County

Hi everyone.

I am trying to filter my query using a checkbox on a form. In the
criteria of a binary field on my query, I am using an IIf statement.
If the box is checked, I want it to return only 0's from the field. If
its not checked, I want it to return both 0's and 1's. It's easy to
make it return either 0's OR 1's, but I can't figure out how to make it
do both.

This is my current criteria:

IIf([Forms]![RIForm]![cbAcq]=True,0,1)

This obviously will return all values that are 0 when the box is
checked. When its not checked, it will return only values that are 1.

I tried dropping down to the next line of my criteria and adding a 0 to
the Or because I'm always going to want either 0's or both 1's and 0's.
However, this still did not filter it correctly.

Any ideas on how I could append my IIf statement to grab both 0's and
1's?

Thanks!