Welcome to SPN

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

Sign Up Now!

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
    >
     

Share This Page