|
 |
access thread: IIf Statement on Check Box to Omit Records
Message #1 by Shelley Curnow <SCurnow@h...> on Mon, 20 May 2002 09:03:46 -0400
|
|
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html;
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version
5.5.2653.12">
<TITLE>IIf Statement on Check Box to Omit Records</TITLE>
</HEAD>
<BODY>
<P><FONT SIZE=3D2>Hello...</FONT>
</P>
<P><FONT SIZE=3D2>I've added a checkbox field to my contact table to
allow users to indicate whether a person has become inactive (Checked
=3D inactive, cleared =3D active).</FONT></P>
<P><FONT SIZE=3D2>I have a quick-search feature that allows users to
search on a variety of criteria and I would like to offer them the
choice to omit records marked inactive from the search. The quick
search form passes values to a query underlying a data display
form.</FONT></P>
<P><FONT SIZE=3D2>I placed an unbound checkbox on the quick-search form
with the caption: Omit Inactive. In the criteria cell of the
query underlying the display form I would like to write a statement
specifying that if the "Omit Inactive" field is checked on
teh quick search form, then omit records marked inactive from the
search, otherwise search for all records meeting the other search
criteria, regardless of whether they are active or inactive
records.</FONT></P>
<P><FONT SIZE=3D2>This is what I wrote:</FONT>
<BR><FONT
SIZE=3D2>IIf([Forms]![frmQuickSearch]![chkOmitInactives]=3DYes,[tblConta
cts].[ContactInactive]<>Yes)</FONT>
</P>
<P><FONT SIZE=3D2>This does omit inactive records if the Omit Inactive
box is checked. However, if the Omit Inactive box is cleared, it
doesn't find any records at all.</FONT></P>
<P><FONT SIZE=3D2>I need help with the "else" part of the IIf
statement. A way to indicate that "else, it doesn't matter
if the record is inactive or active". I had hoped that by
not specifying an "else" part, that would take care of
it. But it seems not...</FONT></P>
<P><FONT SIZE=3D2>Any suggestions?</FONT>
</P>
<P><FONT SIZE=3D2>Thanks in advance....</FONT>
</P>
<P><FONT SIZE=3D2>--Shelley</FONT>
</P>
<P><FONT SIZE=3D2>Shelley Curnow</FONT>
<BR><FONT SIZE=3D2>Grants and Data Administrator</FONT>
<BR><FONT SIZE=3D2>F.B. Heron Foundation</FONT>
</P>
</BODY>
</HTML>
Message #2 by "Leo Scott" <leoscott@c...> on Mon, 20 May 2002 00:55:09 -0700
|
|
IIf([Forms]![frmQuickSearch]![chkOmitInactives]=Yes, _
[tblContacts].[ContactInactive]<>Yes, _
[tblContacts].[ContactInactive]<>No)
I think that would work.
-----Original Message-----
From: Shelley Curnow [mailto:SCurnow@h...]
Sent: Monday, May 20, 2002 6:04 AM
To: Access
Subject: [access] IIf Statement on Check Box to Omit Records
Hello...
I've added a checkbox field to my contact table to allow users to indicate
whether a person has become inactive (Checked = inactive, cleared = active).
I have a quick-search feature that allows users to search on a variety of
criteria and I would like to offer them the choice to omit records marked
inactive from the search. The quick search form passes values to a query
underlying a data display form.
I placed an unbound checkbox on the quick-search form with the caption: Omit
Inactive. In the criteria cell of the query underlying the display form I
would like to write a statement specifying that if the "Omit Inactive" field
is checked on teh quick search form, then omit records marked inactive from
the search, otherwise search for all records meeting the other search
criteria, regardless of whether they are active or inactive records.
This is what I wrote:
IIf([Forms]![frmQuickSearch]![chkOmitInactives]=Yes,[tblContacts].[ContactIn
active]<>Yes)
This does omit inactive records if the Omit Inactive box is checked.
However, if the Omit Inactive box is cleared, it doesn't find any records at
all.
I need help with the "else" part of the IIf statement. A way to indicate
that "else, it doesn't matter if the record is inactive or active". I had
hoped that by not specifying an "else" part, that would take care of it.
But it seems not...
Any suggestions?
Thanks in advance....
--Shelley
Shelley Curnow
Grants and Data Administrator
F.B. Heron Foundation
Message #3 by Shelley Curnow <SCurnow@h...> on Mon, 20 May 2002 12:50:39 -0400
|
|
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html;
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version
5.5.2653.12">
<TITLE>RE: [access] RE: IIf Statement on Check Box to Omit
Records</TITLE>
</HEAD>
<BODY>
<P><FONT SIZE=3D2>Hi, Leo...thanks for the response. Wouldn't the
query you suggested then only pull inactive records if "Omit
Inactives" is cleared?</FONT></P>
<P><FONT SIZE=3D2>What I hope to have happen is this:</FONT>
</P>
<P><FONT SIZE=3D2>Say I have five records, marked as follows:</FONT>
<BR><FONT SIZE=3D2>Record 1 Inactive
(tblContacts.ContactInactive =3D Yes)</FONT>
<BR><FONT SIZE=3D2>Record 2 Active
(tblContacts.ContactInactive =3D No)</FONT>
<BR><FONT SIZE=3D2>Record 3 Active</FONT>
<BR><FONT SIZE=3D2>Record 4 Inactive</FONT>
<BR><FONT SIZE=3D2>Record 5 Active</FONT>
</P>
<P><FONT SIZE=3D2>If on my Quick Search form I search for record #'s
between 1 and 5, and select to omit inactive records
([Forms]![frmQuickSearch]![chkOmitInactive] =3D Yes), I want my found
set to look like this:</FONT></P>
<P><FONT SIZE=3D2>Record 2 Active </FONT>
<BR><FONT SIZE=3D2>Record 3 Active</FONT>
<BR><FONT SIZE=3D2>Record 5 Active</FONT>
</P>
<P><FONT SIZE=3D2>If, however, on my Quick Search form I search for
record #'s between 1 and 5, but clear the "omit inactives"
check box ([Forms]![frmQuickSearch]![chkOmitInactive] =3D No), I want
my found set to look like this:</FONT></P>
<P><FONT SIZE=3D2>Record 1 Inactive </FONT>
<BR><FONT SIZE=3D2>Record 2 Active </FONT>
<BR><FONT SIZE=3D2>Record 3 Active</FONT>
<BR><FONT SIZE=3D2>Record 4 Inactive</FONT>
<BR><FONT SIZE=3D2>Record 5 Active</FONT>
</P>
<P><FONT SIZE=3D2>I think the query you suggested would return the
following...</FONT>
</P>
<P><FONT SIZE=3D2>Searching for records between 1-5, "Omit
Inactives" checked:</FONT>
</P>
<P><FONT SIZE=3D2>Record 2 Active </FONT>
<BR><FONT SIZE=3D2>Record 3 Active</FONT>
<BR><FONT SIZE=3D2>Record 5 Active</FONT>
</P>
<P><FONT SIZE=3D2>Searching for records between 1-5, "Omit
Inactives" cleared:</FONT>
</P>
<P><FONT SIZE=3D2>Record 1 Inactive </FONT>
<BR><FONT SIZE=3D2>Record 4 Inactive</FONT>
</P>
<BR>
<P><FONT SIZE=3D2>I don't have my database in front of me so I can't
check it out, but I think that's what would happen. But what I
want is to be able to find all records if I clear the "Omit
Inactives" check box.</FONT></P>
<P><FONT SIZE=3D2>Any ideas?</FONT>
</P>
<P><FONT SIZE=3D2>---Shelley</FONT>
</P>
<P><FONT SIZE=3D2>Shelley Curnow</FONT>
<BR><FONT SIZE=3D2>Grants and Data Administrator</FONT>
<BR><FONT SIZE=3D2>F.B. Heron Foundation</FONT>
</P>
<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: Leo Scott [<A
HREF=3D"mailto:leoscott@c...">mailto:leoscott@c...</A>]</FONT>
<BR><FONT SIZE=3D2>Sent: Monday, May 20, 2002 3:55 AM</FONT>
<BR><FONT SIZE=3D2>To: Access</FONT>
<BR><FONT SIZE=3D2>Subject: [access] RE: IIf Statement on Check Box to
Omit Records</FONT>
</P>
<P><FONT
SIZE=3D2>IIf([Forms]![frmQuickSearch]![chkOmitInactives]=3DYes,
_</FONT>
<BR><FONT SIZE=3D2> [tblContacts].[ContactInactive]<>Yes,
_</FONT>
<BR><FONT
SIZE=3D2> [tblContacts].[ContactInactive]<>No)</FONT>
</P>
<P><FONT SIZE=3D2>I think that would work.</FONT>
</P>
<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: Shelley Curnow [<A
HREF=3D"mailto:SCurnow@h...">mailto:SCurnow@h...</A>]</F
ONT>
<BR><FONT SIZE=3D2>Sent: Monday, May 20, 2002 6:04 AM</FONT>
<BR><FONT SIZE=3D2>To: Access</FONT>
<BR><FONT SIZE=3D2>Subject: [access] IIf Statement on Check Box to Omit
Records</FONT>
</P>
<BR>
<P><FONT SIZE=3D2>Hello...</FONT>
<BR><FONT SIZE=3D2>I've added a checkbox field to my contact table to
allow users to indicate</FONT>
<BR><FONT SIZE=3D2>whether a person has become inactive (Checked =3D
inactive, cleared =3D active).</FONT>
<BR><FONT SIZE=3D2>I have a quick-search feature that allows users to
search on a variety of</FONT>
<BR><FONT SIZE=3D2>criteria and I would like to offer them the choice
to omit records marked</FONT>
<BR><FONT SIZE=3D2>inactive from the search. The quick search
form passes values to a query</FONT>
<BR><FONT SIZE=3D2>underlying a data display form.</FONT>
<BR><FONT SIZE=3D2>I placed an unbound checkbox on the quick-search
form with the caption: Omit</FONT>
<BR><FONT SIZE=3D2>Inactive. In the criteria cell of the query
underlying the display form I</FONT>
<BR><FONT SIZE=3D2>would like to write a statement specifying that if
the "Omit Inactive" field</FONT>
<BR><FONT SIZE=3D2>is checked on teh quick search form, then omit
records marked inactive from</FONT>
<BR><FONT SIZE=3D2>the search, otherwise search for all records meeting
the other search</FONT>
<BR><FONT SIZE=3D2>criteria, regardless of whether they are active or
inactive records.</FONT>
<BR><FONT SIZE=3D2>This is what I wrote:</FONT>
</P>
<P><FONT
SIZE=3D2>IIf([Forms]![frmQuickSearch]![chkOmitInactives]=3DYes,[tblConta
cts].[ContactIn</FONT>
<BR><FONT SIZE=3D2>active]<>Yes)</FONT>
</P>
<P><FONT SIZE=3D2>This does omit inactive records if the Omit Inactive
box is checked.</FONT>
<BR><FONT SIZE=3D2>However, if the Omit Inactive box is cleared, it
doesn't find any records at</FONT>
<BR><FONT SIZE=3D2>all.</FONT>
<BR><FONT SIZE=3D2>I need help with the "else" part of the
IIf statement. A way to indicate</FONT>
<BR><FONT SIZE=3D2>that "else, it doesn't matter if the record is
inactive or active". I had</FONT>
<BR><FONT SIZE=3D2>hoped that by not specifying an "else"
part, that would take care of it.</FONT>
<BR><FONT SIZE=3D2>But it seems not...</FONT>
<BR><FONT SIZE=3D2>Any suggestions?</FONT>
<BR><FONT SIZE=3D2>Thanks in advance....</FONT>
<BR><FONT SIZE=3D2>--Shelley</FONT>
<BR><FONT SIZE=3D2>Shelley Curnow</FONT>
<BR><FONT SIZE=3D2>Grants and Data Administrator</FONT>
<BR><FONT SIZE=3D2>F.B. Heron Foundation</FONT>
<BR><FONT SIZE=3D2>---</FONT>
<BR><FONT SIZE=3D2>Change your mail options at <A
HREF=3D"http://p2p.wrox.com/manager.asp"
TARGET=3D"_blank">http://p2p.wrox.com/manager.asp</A> or</FONT>
<BR><FONT SIZE=3D2>
</FONT>
</P>
<BR>
<P><FONT SIZE=3D2>---</FONT>
<BR><FONT SIZE=3D2>Change your mail options at <A
HREF=3D"http://p2p.wrox.com/manager.asp"
TARGET=3D"_blank">http://p2p.wrox.com/manager.asp</A> or</FONT>
<BR><FONT SIZE=3D2>
</FONT>
</P>
</BODY>
</HTML>
Message #4 by ProDev <prodevmg@y...> on Mon, 20 May 2002 06:45:11 -0700 (PDT)
|
|
--0-2109826207-1021902311=:17557
Content-Type: text/plain; charset=us-ascii
In the criteria section of the field in question I would put...
IIf([Forms]![frmQuickSearch]![chkOmitInactives]=True,True,False)
Hope this helps.
Shelley Curnow <SCurnow@h...> wrote:
Hello...
I've added a checkbox field to my contact table to allow users to indicate whether a person has become inactive (Checked = inactive,
cleared = active).
I have a quick-search feature that allows users to search on a variety of criteria and I would like to offer them the choice to omit
records marked inactive from the search. The quick search form passes values to a query underlying a data display form.
I placed an unbound checkbox on the quick-search form with the caption: Omit Inactive. In the criteria cell of the query underlying
the display form I would like to write a statement specifying that if the "Omit Inactive" field is checked on teh quick search form,
then omit records marked inactive from the search, otherwise search for all records meeting the other search criteria, regardless of
whether they are active or inactive records.
This is what I wrote:
IIf([Forms]![frmQuickSearch]![chkOmitInactives]=Yes,[tblContacts].[ContactInactive]<>Yes)
This does omit inactive records if the Omit Inactive box is checked. However, if the Omit Inactive box is cleared, it doesn't find
any records at all.
I need help with the "else" part of the IIf statement. A way to indicate that "else, it doesn't matter if the record is inactive or
active". I had hoped that by not specifying an "else" part, that would take care of it. But it seems not...
Any suggestions?
Thanks in advance....
--Shelley
Shelley Curnow
Grants and Data Administrator
F.B. Heron Foundation
---
Lonnie Johnson
ProDev, Builders of MS Access Databases
http://www.galaxymall.com/software/PRODEV
---------------------------------
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
Message #5 by Shelley Curnow <SCurnow@h...> on Mon, 20 May 2002 13:58:07 -0400
|
|
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C20027.E5453DC0
Content-Type: text/plain;
charset="iso-8859-1"
Sorry, not sure I follow the logic behind how this is different (except
using True/False instead of Yes/No). Could you explain a little more?
Thanks for the response...
--Shelley
Shelley Curnow
Grants and Data Administrator
F.B. Heron Foundation
-----Original Message-----
From: ProDev [mailto:prodevmg@y...]
Sent: Monday, May 20, 2002 9:45 AM
To: Access
Subject: [access] Re: IIf Statement on Check Box to Omit Records
In the criteria section of the field in question I would put...
IIf([Forms]![frmQuickSearch]![chkOmitInactives]=True,True,False)
Hope this helps.
Shelley Curnow <SCurnow@h...> wrote:
Hello...
I've added a checkbox field to my contact table to allow users to indicate
whether a person has become inactive (Checked = inactive, cleared = active).
I have a quick-search feature that allows users to search on a variety of
criteria and I would like to offer them the choice to omit records marked
inactive from the search. The quick search form passes values to a query
underlying a data display form.
I placed an unbound checkbox on the quick-search form with the caption: Omit
Inactive. In the criteria cell of the query underlying the display form I
would like to write a statement specifying that if the "Omit Inactive" field
is checked on teh quick search form, then omit records marked inactive from
the search, otherwise search for all records meeting the other search
criteria, regardless of whether they are active or inactive records.
This is what I wrote:
IIf([Forms]![frmQuickSearch]![chkOmitInactives]=Yes,[tblContacts].[ContactIn
active]<>Yes)
This does omit inactive records if the Omit Inactive box is checked.
However, if the Omit Inactive box is cleared, it doesn't find any records at
all.
I need help with the "else" part of the IIf statement. A way to indicate
that "else, it doesn't matter if the record is inactive or active". I had
hoped that by not specifying an "else" part, that would take care of it.
But it seems not...
Any suggestions?
Thanks in advance....
--Shelley
Shelley Curnow
Grants and Data Administrator
F.B. Heron Foundation
---
Lonnie Johnson
ProDev, Builders of MS Access Databases
http://www.galaxymall.com/software/PRODEV
<http://www.galaxymall.com/software/PRODEV%20>
_____
Do You Yahoo!?
LAUNCH <http://rd.yahoo.com/welcome/*http://launch.yahoo.com> - Your Yahoo!
Music Experience ---
http://p2p.wrox.com/manager.asp or
Message #6 by ProDev <prodevmg@y...> on Mon, 20 May 2002 12:33:03 -0700 (PDT)
|
|
--0-2005465494-1021923183=:4091
Content-Type: text/plain; charset=us-ascii
Actually it is no logical difference, just verbal structure. I noticed that you were using the table name in the 'Then' conditions.
Also the IIf statement is a little touchy about which syntax is used, so I was offering an alternative.
Shelley Curnow <SCurnow@h...> wrote:
v\:* {behavior:url(#default#VML);}o\:* {behavior:url(#default#VML);}w\:* {behavior:url(#default#VML);}.shape
{behavior:url(#default#VML);}
Sorry, not sure I follow the logic behind how this is different (except using True/False instead of Yes/No). Could you explain a
little more?
Thanks for the response?
--Shelley
Shelley Curnow
Grants and Data Administrator
F.B. Heron Foundation
-----Original Message-----
From: ProDev [mailto:prodevmg@y...]
Sent: Monday, May 20, 2002 9:45 AM
To: Access
Subject: [access] Re: IIf Statement on Check Box to Omit Records
In the criteria section of the field in question I would put...
IIf([Forms]![frmQuickSearch]![chkOmitInactives]=True,True,False)
Hope this helps.
Shelley Curnow <SCurnow@h...> wrote:
Hello...
I've added a checkbox field to my contact table to allow users to indicate whether a person has become inactive (Checked = inactive,
cleared = active).
I have a quick-search feature that allows users to search on a variety of criteria and I would like to offer them the choice to omit
records marked inactive from the search. The quick search form passes values to a query underlying a data display form.
I placed an unbound checkbox on the quick-search form with the caption: Omit Inactive. In the criteria cell of the query underlying
the display form I would like to write a statement specifying that if the "Omit Inactive" field is checked on teh quick search form,
then omit records marked inactive from the search, otherwise search for all records meeting the other search criteria, regardless of
whether they are active or inactive records.
This is what I wrote:
IIf([Forms]![frmQuickSearch]![chkOmitInactives]=Yes,[tblContacts].[ContactInactive]<>Yes)
This does omit inactive records if the Omit Inactive box is checked. However, if the Omit Inactive box is cleared, it doesn't find
any records at all.
I need help with the "else" part of the IIf statement. A way to indicate that "else, it doesn't matter if the record is inactive or
active". I had hoped that by not specifying an "else" part, that would take care of it. But it seems not...
Any suggestions?
Thanks in advance....
--Shelley
Shelley Curnow
Grants and Data Administrator
F.B. Heron Foundation
---
Lonnie Johnson
ProDev, Builders of MS Access Databases
http://www.galaxymall.com/software/PRODEV
---------------------------------
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience --- Change your mail options at http://p2p.wrox.com/manager.asp or to unsubscribe send a blank
email to
---
Lonnie Johnson
ProDev, Builders of MS Access Databases
http://www.galaxymall.com/software/PRODEV
---------------------------------
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
Message #7 by "Amy Wyatt" <amyw@c...> on Tue, 21 May 2002 14:22:29
|
|
I would suggest doing a filter on the Display form to accomplish. If the
box is checked add this to the form's filter i.e.:
Forms!frmName.FilterOn=True
Forms!frmName.Filter="ContactInactive = False"
if the box is not checked then it would be:
Forms!frmName.FilterOn=False
Forms!frmName.Filter=""
Otherwise you could try
IIF .... , ,[tblContacts].[ContactInactive]=0,[tblContacts].
[ContactInactive]<>12
Since a yes/no field is a boolean, 0 = false or No and -1 or 1 = True or
Yes and setting the criteria to <>12 will give you both the
True/False/Yes/No values for the field.
Hope this helps,
Amy
I've added a checkbox field to my contact table to
allow users to indicate whether a person has become inactive (Checked
=3D inactive, cleared =3D active).
I have a quick-search feature that allows users to
search on a variety of criteria and I would like to offer them the
choice to omit records marked inactive from the search. The quick
search form passes values to a query underlying a data display
form.
I placed an unbound checkbox on the quick-search form
with the caption: Omit Inactive. In the criteria cell of the
query underlying the display form I would like to write a statement
specifying that if the "Omit Inactive" field is checked on
teh quick search form, then omit records marked inactive from the
search, otherwise search for all records meeting the other search
criteria, regardless of whether they are active or inactive
records.
This is what I wrote:
IIf([Forms]![frmQuickSearch]![chkOmitInactives]=3DYes,[tblConta
cts].[ContactInactive]<>Yes)
This does omit inactive records if the Omit Inactive
box is checked. However, if the Omit Inactive box is cleared, it
doesn't find any records at all.
I need help with the "else" part of the IIf
statement. A way to indicate that "else, it doesn't matter
if the record is inactive or active". I had hoped that by
not specifying an "else" part, that would take care of
it. But it seems not...
Any suggestions?
Thanks in advance....
--Shelley
</P>
<P><FONT SIZE=3D2>Shelley Curnow</FONT>
<BR><FONT SIZE=3D2>Grants and Data Administrator</FONT>
<BR><FONT SIZE=3D2>F.B. Heron Foundation</FONT>
</P>
</BODY>
</HTML>
|
|
|
View Cart
