Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

Checkbox Validation Rule

Discussion in 'Information Technology' started by dmc14, Nov 18, 2005.

  1. dmc14

    dmc14
    Expand Collapse
    Guest

    I have a subform that has 50 checkboxes, which are one for each state.
    This is used in the database to select which states we do not mail to
    for certain programs. There is also a check box for "none" which means
    that no states are excluded and we can mail to all 50 states.

    I have a validation rule on each of the 50 states that says "[None]=0
    Or Is Null" which works fine.

    I also want a validation rule on None that will not let you check that
    box if any one of the 50 states is selected. I am trying to use "[AL]
    And [AK] And [AZ] And [AR] And [CA] And [CO] And [CT] And [DE] And [DC]
    And [FL] And [GA] And [HI] And [ID] And [IL] And [IN] And [IA] And [KA]
    And [KY] And [LA] And [ME] And [MD] And [MA] And [MI] And [MN] And [MS]
    And [MO] And [MT] And [NE] And [NV] And [NH] And [NJ] And [NM] And [NY]
    And [NC] And [ND] And [OH] And [OK] And [OR] And [PA] And [RI] And [SC]
    And [SD] And [TN] And [TX] And [UT] And [VT] And [VA] And [WA] And [WV]
    And [WI] And [WY] And [APO] Is Null Or Or 0", but it will not work. It
    keeps telling me that a box is checked even when none are.

    Does anyone know what I am doing wrong?

    Thanks,

    David


    --
    dmc14Posted from - http://www.officehelp.in
     
  2. Loading...

    Similar Threads Forum Date
    Has this story any validation in Sikhism ? Sikh Sikhi Sikhism Sep 3, 2004

  3. Arvin Meyer [MVP]

    Arvin Meyer [MVP]
    Expand Collapse
    Guest

    The end of it:

    And [APO] Is Null Or Or 0"

    should be:

    And [APO] Is Null Or [APO] = 0"

    but only if you want the APO text box to be one of them. APO could also = ""
    and pass. To exclude that use:

    And [APO] Is Null OR [APO] = "" Or [APO] = 0"


    --
    Arvin Meyer, MCP, MVP
    Microsoft Access
    Free Access downloads
    http://www.datastrat.com
    http://www.mvps.org/access


    "dmc14" wrote:

    >
    > I have a subform that has 50 checkboxes, which are one for each state.
    > This is used in the database to select which states we do not mail to
    > for certain programs. There is also a check box for "none" which means
    > that no states are excluded and we can mail to all 50 states.
    >
    > I have a validation rule on each of the 50 states that says "[None]=0
    > Or Is Null" which works fine.
    >
    > I also want a validation rule on None that will not let you check that
    > box if any one of the 50 states is selected. I am trying to use "[AL]
    > And [AK] And [AZ] And [AR] And [CA] And [CO] And [CT] And [DE] And [DC]
    > And [FL] And [GA] And [HI] And [ID] And [IL] And [IN] And [IA] And [KA]
    > And [KY] And [LA] And [ME] And [MD] And [MA] And [MI] And [MN] And [MS]
    > And [MO] And [MT] And [NE] And [NV] And [NH] And [NJ] And [NM] And [NY]
    > And [NC] And [ND] And [OH] And [OK] And [OR] And [PA] And [RI] And [SC]
    > And [SD] And [TN] And [TX] And [UT] And [VT] And [VA] And [WA] And [WV]
    > And [WI] And [WY] And [APO] Is Null Or Or 0", but it will not work. It
    > keeps telling me that a box is checked even when none are.
    >
    > Does anyone know what I am doing wrong?
    >
    > Thanks,
    >
    > David
    >
    >
    > --
    > dmc14Posted from - http://www.officehelp.in
    >
    >
     
  4. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    You can't chain boolean expressions together like that.

    Checkboxes are either Null (gray), 0 (unchecked) or -1 (checked).

    To determine if any of the checkboxes are checked, you can add the values of
    the checkboxes together: if it doesn't equal 0, at least one is checked. To
    handle Nulls, use the Nz() function to convert the Null to 0:

    (Nz([AL], 0) + Nz([AK], 0) + Nz([AZ], 0) + ... +
    Nz([WI],0) + Nz([WY],0) + Nz([APO],0))

    Alternatively, you can use Nz() to convert Null checkboxes to False, and Or
    the values of them together:

    (Nz([AL], False) Or Nz([AK], False) Or Nz([AZ], False) Or ... Or
    Nz([WI],False) Or Nz([WY],False) Or Nz([APO],False))

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "dmc14" <dmc14.1yn9fj@NoSpamPleaze.com> wrote in message
    news:dmc14.1yn9fj@NoSpamPleaze.com...
    >
    > I have a subform that has 50 checkboxes, which are one for each state.
    > This is used in the database to select which states we do not mail to
    > for certain programs. There is also a check box for "none" which means
    > that no states are excluded and we can mail to all 50 states.
    >
    > I have a validation rule on each of the 50 states that says "[None]=0
    > Or Is Null" which works fine.
    >
    > I also want a validation rule on None that will not let you check that
    > box if any one of the 50 states is selected. I am trying to use "[AL]
    > And [AK] And [AZ] And [AR] And [CA] And [CO] And [CT] And [DE] And [DC]
    > And [FL] And [GA] And [HI] And [ID] And [IL] And [IN] And [IA] And [KA]
    > And [KY] And [LA] And [ME] And [MD] And [MA] And [MI] And [MN] And [MS]
    > And [MO] And [MT] And [NE] And [NV] And [NH] And [NJ] And [NM] And [NY]
    > And [NC] And [ND] And [OH] And [OK] And [OR] And [PA] And [RI] And [SC]
    > And [SD] And [TN] And [TX] And [UT] And [VT] And [VA] And [WA] And [WV]
    > And [WI] And [WY] And [APO] Is Null Or Or 0", but it will not work. It
    > keeps telling me that a box is checked even when none are.
    >
    > Does anyone know what I am doing wrong?
    >
    > Thanks,
    >
    > David
    >
    >
    > --
    > dmc14Posted from - http://www.officehelp.in
    >
     
  5. dmc14

    dmc14
    Expand Collapse
    Guest

    Thanks for everyone's help. While I either did your suggestions wrong,
    or I just didn't know what I was doing, I couldn't get either of them
    to work.

    However, by using what you told me, I figured out a solution that seems
    to work just fine. I changed the validation rule for none to:

    [AL]+[AK]+[AR]+[CA]+[CO]+[CT]+[DE]+[DC]+[FL]+[GA]+[HI]+[ID]+[IL]+[IN]+
    [IA]+[KA]+[KY]+[LA]+[ME]+[MD]+[MA]+[MI]+[MN]+[MS]+[MO]+[MT]+[NE]+
    [NV]+[NH]+[NJ]+[NM]+[NY]+[NC]+[ND]+[OH]+[OK]+[OR]+[PA]+[RI]+[SC]+
    [SD]+[TN]+[TX]+[UT]+[VT]+[VA]+[WA]+[WV]+[WI]+[WY]+[APO]>=0

    That worked fine.

    Thanks for the tips.

    David


    --
    dmc14Posted from - http://www.officehelp.in
     
  6. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    It shouldn't work at all!

    Since a checked checkbox has a value of -1, the sum can never be greater
    than 0.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)



    "dmc14" <dmc14.1ynsqe@NoSpamPleaze.com> wrote in message
    news:dmc14.1ynsqe@NoSpamPleaze.com...
    >
    > Thanks for everyone's help. While I either did your suggestions wrong,
    > or I just didn't know what I was doing, I couldn't get either of them
    > to work.
    >
    > However, by using what you told me, I figured out a solution that seems
    > to work just fine. I changed the validation rule for none to:
    >
    > [AL]+[AK]+[AR]+[CA]+[CO]+[CT]+[DE]+[DC]+[FL]+[GA]+[HI]+[ID]+[IL]+[IN]+
    > [IA]+[KA]+[KY]+[LA]+[ME]+[MD]+[MA]+[MI]+[MN]+[MS]+[MO]+[MT]+[NE]+
    > [NV]+[NH]+[NJ]+[NM]+[NY]+[NC]+[ND]+[OH]+[OK]+[OR]+[PA]+[RI]+[SC]+
    > [SD]+[TN]+[TX]+[UT]+[VT]+[VA]+[WA]+[WV]+[WI]+[WY]+[APO]>=0
    >
    > That worked fine.
    >
    > Thanks for the tips.
    >
    > David
    >
    >
    > --
    > dmc14Posted from - http://www.officehelp.in
    >
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page