Welcome to SPN

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

Sign Up Now!

Validation rule to prevent entry

Discussion in 'Information Technology' started by simonc, Jul 28, 2006.

  1. simonc

    simonc
    Expand Collapse
    Guest

    I am designing a table in which one field has two possible values, and a
    second field must be filled if the first field has one value, and must be
    empty if the first field has the other value. (Hope that makes sense.)

    How can I implement this? Is there some expression I can put in the
    validation rule which will enforce this?

    Grateful for any help
     
  2. Loading...


  3. Ofer Cohen

    Ofer Cohen
    Expand Collapse
    Guest

    You can do that using a form to enter data in the table.

    On the Before update event of the form, you can add the code to validate the
    entry

    If Me.Field1 = "Value1" And Len(Me.Field2 & "")=0 Then
    MsgBox "Field2 must be filled"
    Else
    If Me.Field1 = "Value2" Then
    Me.Field2 = Null
    End If
    End If

    --
    Good Luck
    BS"D


    "simonc" wrote:

    > I am designing a table in which one field has two possible values, and a
    > second field must be filled if the first field has one value, and must be
    > empty if the first field has the other value. (Hope that makes sense.)
    >
    > How can I implement this? Is there some expression I can put in the
    > validation rule which will enforce this?
    >
    > Grateful for any help
     
  4. Ofer Cohen

    Ofer Cohen
    Expand Collapse
    Guest

    I forgot one thing

    If Me.Field1 = "Value1" And Len(Me.Field2 & "")=0 Then
    MsgBox "Field2 must be filled"
    Cancel = True 'wont let the user exit until the field is filled
    Else
    If Me.Field1 = "Value2" Then
    Me.Field2 = Null
    End If
    End If


    --
    Good Luck
    BS"D


    "Ofer Cohen" wrote:

    > You can do that using a form to enter data in the table.
    >
    > On the Before update event of the form, you can add the code to validate the
    > entry
    >
    > If Me.Field1 = "Value1" And Len(Me.Field2 & "")=0 Then
    > MsgBox "Field2 must be filled"
    > Else
    > If Me.Field1 = "Value2" Then
    > Me.Field2 = Null
    > End If
    > End If
    >
    > --
    > Good Luck
    > BS"D
    >
    >
    > "simonc" wrote:
    >
    > > I am designing a table in which one field has two possible values, and a
    > > second field must be filled if the first field has one value, and must be
    > > empty if the first field has the other value. (Hope that makes sense.)
    > >
    > > How can I implement this? Is there some expression I can put in the
    > > validation rule which will enforce this?
    > >
    > > Grateful for any help
     
  5. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    Ofer Cohen wrote:
    > > I am designing a table in which one field has two possible values, and a
    > > second field must be filled if the first field has one value, and must be
    > > empty if the first field has the other value. (Hope that makes sense.)
    > >
    > > How can I implement this? Is there some expression I can put in the
    > > validation rule which will enforce this?

    >
    > You can do that using a form to enter data in the table.


    The OP asked for a Validation Rule, which is wise because *only*
    putting validation in the front end application is not good idea (see
    http://www.dbazine.com/ofinterest/oi-articles/celko25).

    I've implemented example Validation Rules as CHECK constraints:

    CREATE TABLE Test8 (

    two_possible_values CHAR(1) DEFAULT 'Y' NOT NULL,
    CONSTRAINT two_possible_values__legal_values
    CHECK (two_possible_values IN ('Y', 'N')),

    dependent_col VARCHAR(20),

    CONSTRAINT dependent_col__never_zero_length
    CHECK (LEN(dependent_col) > 0),

    CONSTRAINT dependent_col__only_when__two_possible_values_col__eq_Y
    CHECK
    (
    IIF(two_possible_values = 'Y', 1, 0)
    =
    IIF(dependent_col IS NULL, 0, 1)
    )
    );

    This fails the rule dependent_col__never_zero_length:
    INSERT INTO Test8 (two_possible_values, dependent_col) VALUES ('Y', '')

    This fails the rule dependent_col__never_zero_length:
    INSERT INTO Test8 (two_possible_values, dependent_col) VALUES ('N',
    '');

    This fails the rule
    dependent_col__only_when__two_possible_values_col__eq_Y:
    INSERT INTO Test8 (two_possible_values, dependent_col) VALUES ('Y',
    NULL);

    This fails the rule
    dependent_col__only_when__two_possible_values_col__eq_Y:
    INSERT INTO Test8 (two_possible_values, dependent_col) VALUES ('N',
    'Because');

    This is legal:
    INSERT INTO Test8 (two_possible_values, dependent_col) VALUES ('Y',
    'Because');

    This is legal:
    INSERT INTO Test8 (two_possible_values, dependent_col) VALUES ('N',
    NULL);

    Jamie.

    --
     

Share This Page