Help with an IIF statement in a query

[alanmorganjr via AccessMonster.com]

I have a query that runs off of a table with a yes/no field called
[not_active]. I also have a form named [Comp_Report_Active_Criteria_frm] with
3 check boxes named [active], [inactive], and [both]. I would like it when:

- the [active] check box is checked the query only displays records where
the [not_active] box is unchecked.
- the [inactive] check box is checked the query only displays records where
the [not_active] box is checked.
- the [both] check box is checked the query displays all the records where
the [not_active] box is both checked and unchecked.

The formula I inserted in the query is as follows:
IIf([Forms]![Comp_Report_Active_Criteria_frm]![Active]=-1,0,IIf([Forms]!
[Comp_Report_Active_Criteria_frm]![Inactive]=-1,-1,IIf([Forms]!
[Comp_Report_Active_Criteria_frm]![Both]=-1,Null)))

The formula works great unless I check the [both] check box at which time the
query returns no records at all.

Any help on how to get this to work will be greatly appreciated.

Thanks,
Alan

 

[Wayne Morgan]

For starters, if I understand correctly you have 2 checkboxes on the form.
One is Active, the other is Inactive. These sound as if they are mutually
exclusive, the item is either Active or Inactive, not both, so there should
be no reason for 2 check boxes. I understand having the 3 selections on the
form, but you also make it sound as if you have 2 fields in the table, one
for Active and one for Inactive. However, it may just be the way your
statement is worded, if so (you only have one field), disregard this part.

First, on the form, I would make this an Option Group. This will limit the
user to one selection at a time. If you use an Option Group, the Windows
convention would be to use radio buttons instead of check boxes, but it'll
work either way. The other option would be to use 2 check boxes that aren't
in an Option Group. This would allow you to check one or both of them. Since
you could check both of them, the Both option wouldn't be needed. You could
use code to keep the user from unchecking both of them simultaneously.

For the criteria in the query, if the selections are in an option group in
the form, the return values will actually be 1, 2, or 3 (or whatever you set
them to be), not True or False for each check box (i.e. the Option Group has
the value, not the individual controls in the Option group). It appears from
your example, that you are using an Option Group.

Example criteria:
IIf([Forms]![Comp_Report_Active_Criteria_frm]![optOptionGroup]=1, True,
IIf([Forms]![Comp_Report_Active_Criteria_frm]![optOptionGroup]=2, False)) Or
([Forms]![Comp_Report_Active_Criteria_frm]![optOptionGroup]=3)

--
Wayne Morgan
MS Access MVP

I have a query that runs off of a table with a yes/no field called
[not_active]. I also have a form named [Comp_Report_Active_Criteria_frm]
with
3 check boxes named [active], [inactive], and [both]. I would like it
when:

- the [active] check box is checked the query only displays records where
the [not_active] box is unchecked.
- the [inactive] check box is checked the query only displays records
where
the [not_active] box is checked.
- the [both] check box is checked the query displays all the records where
the [not_active] box is both checked and unchecked.

The formula I inserted in the query is as follows:
IIf([Forms]![Comp_Report_Active_Criteria_frm]![Active]=-1,0,IIf([Forms]!
[Comp_Report_Active_Criteria_frm]![Inactive]=-1,-1,IIf([Forms]!
[Comp_Report_Active_Criteria_frm]![Both]=-1,Null)))

The formula works great unless I check the [both] check box at which time
the
query returns no records at all.

Any help on how to get this to work will be greatly appreciated.

Thanks,
Alan

 

[Guest]

Hi Alan,
You should have let Access do the work for you. You could have created an
Option group on the form with Active, Inactive, and Both boxes instead of the
three check boxes. You could make the Option Values -1, 0, and 2 for both.
You will still need the IIf to make the 2 something like <2

Give Option Groups a try. It's right there on the form toolbox in design
mode. A wizard will walk you through it.

To fix what you have now, make the last statement something like <2 instead
of Null. Less than 2 will cover both -1 and 0. to give you both.

Question: Do you have something that stops two or more of the check boxes
being selected at once? If not, your IIf could cause problems. Again another
reason for an Option Group which only allows one selection.

 

[John Spencer]

You might try the following

WHERE YourField =
IIf([Forms]![Comp_Report_Active_Criteria_frm]![Active]=-1,0,
IIf([Forms]![Comp_Report_Active_Criteria_frm]![Inactive]=-1,-1))
OR
[Forms]![Comp_Report_Active_Criteria_frm]![Both]=True


In the query grid
Field: YourField
Criteria(line 1):
IIf([Forms]![Comp_Report_Active_Criteria_frm]![Active]=-1,0,
IIf([Forms]![Comp_Report_Active_Criteria_frm]![Inactive]=-1,-1))

Field: [Forms]![Comp_Report_Active_Criteria_frm]![Both]
Criteria (Line 2): True

 

[alanmorganjr via AccessMonster.com]

Wayne,

Thanks for responding. I appologize that my explanation wasn't very clear.

On my table I have one field that is formatted as a yes/no checkbox.

On my form I have three check boxes titled; [active], [inactive], and [both].

When I generate a report from the query I would like to have the option of
reporting just the records that are active, just the records that are
incative, or the option to generate a comprehensive report of both active and
inactive.

I hope that this helps to make my literary mess a little more understandable.


Thanks again for the help

For starters, if I understand correctly you have 2 checkboxes on the form.
One is Active, the other is Inactive. These sound as if they are mutually
exclusive, the item is either Active or Inactive, not both, so there should
be no reason for 2 check boxes. I understand having the 3 selections on the
form, but you also make it sound as if you have 2 fields in the table, one
for Active and one for Inactive. However, it may just be the way your
statement is worded, if so (you only have one field), disregard this part.

First, on the form, I would make this an Option Group. This will limit the
user to one selection at a time. If you use an Option Group, the Windows
convention would be to use radio buttons instead of check boxes, but it'll
work either way. The other option would be to use 2 check boxes that aren't
in an Option Group. This would allow you to check one or both of them. Since
you could check both of them, the Both option wouldn't be needed. You could
use code to keep the user from unchecking both of them simultaneously.

For the criteria in the query, if the selections are in an option group in
the form, the return values will actually be 1, 2, or 3 (or whatever you set
them to be), not True or False for each check box (i.e. the Option Group has
the value, not the individual controls in the Option group). It appears from
your example, that you are using an Option Group.

Example criteria:
IIf([Forms]![Comp_Report_Active_Criteria_frm]![optOptionGroup]=1, True,
IIf([Forms]![Comp_Report_Active_Criteria_frm]![optOptionGroup]=2, False)) Or
([Forms]![Comp_Report_Active_Criteria_frm]![optOptionGroup]=3)

I have a query that runs off of a table with a yes/no field called
[not_active]. I also have a form named [Comp_Report_Active_Criteria_frm]

[quoted text clipped - 23 lines]

Thanks,
Alan

 

[alanmorganjr via AccessMonster.com]

Thank you every one for your suggestions. Because of them we were able to get
it to work.

Thanks a lot,

Alan

You might try the following

WHERE YourField =
IIf([Forms]![Comp_Report_Active_Criteria_frm]![Active]=-1,0,
IIf([Forms]![Comp_Report_Active_Criteria_frm]![Inactive]=-1,-1))
OR
[Forms]![Comp_Report_Active_Criteria_frm]![Both]=True

In the query grid
Field: YourField
Criteria(line 1):
IIf([Forms]![Comp_Report_Active_Criteria_frm]![Active]=-1,0,
IIf([Forms]![Comp_Report_Active_Criteria_frm]![Inactive]=-1,-1))

Field: [Forms]![Comp_Report_Active_Criteria_frm]![Both]
Criteria (Line 2): True

I have a query that runs off of a table with a yes/no field called
[not_active]. I also have a form named [Comp_Report_Active_Criteria_frm]

[quoted text clipped - 23 lines]

Thanks,
Alan