Help with an IIF statement in a query
in [Queries]
|
Prev: SQL in VBA code ?
Next: Query by dates
From: alanmorganjr via AccessMonster.com on 10 Mar 2006 11:06 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200603/1
From: Wayne Morgan on 10 Mar 2006 11:35 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 "alanmorganjr via AccessMonster.com" <u11037(a)uwe> wrote in message news:5d0d6c1df8718(a)uwe... >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 > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200603/1
From: Jerry Whittle on 10 Mar 2006 11:46 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. -- Jerry Whittle Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "alanmorganjr via AccessMonster.com" wrote: > 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 > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200603/1 >
From: John Spencer on 10 Mar 2006 11:47 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" <u11037(a)uwe> wrote in message news:5d0d6c1df8718(a)uwe... >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 > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200603/1
From: alanmorganjr via AccessMonster.com on 10 Mar 2006 12:06
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 Wayne Morgan wrote: >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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200603/1 |