Validation rule to prevent entry

[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

 

[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

 

[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

 

[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

 

[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

 

[Jamie Collins]

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.

--

 

[Jamie Collins]

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.

--