Validate a field based on another field

  • Thread starter Thread starter DSmith
  • Start date Start date
D

DSmith

Is it possible to write a validation rule for a field based on another
field? I have a field [CertType] that only gets filled in if the
[CourseStatus] field is filled in with a P otherwise it stays null. Help
would be greatly appreciated.
 
Open your table in design view.
Open the Properties box (View menu).
Enter a Validation Rule in the Properties box. This is the rule for the
table - different to the Validation Rule in the lower pane of table design,
which applies to a field.

To insist that CertType can have a value only if CourseStatus is P, enter
something like this:

([CourseStatus] = "P" AND [CertType] Is Not Null) OR ([CertType Is Null)
 
Allen,
I did as you suggested. It works great if anything but P is entered in
CourseStatus but if P is entered it allows CertType to be null also. Is
there a way that it would also require that a value be inputted in CertType
if CourseStatus is P?

Thanks,
Donna

Allen Browne said:
Open your table in design view.
Open the Properties box (View menu).
Enter a Validation Rule in the Properties box. This is the rule for the
table - different to the Validation Rule in the lower pane of table design,
which applies to a field.

To insist that CertType can have a value only if CourseStatus is P, enter
something like this:

([CourseStatus] = "P" AND [CertType] Is Not Null) OR ([CertType Is Null)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

DSmith said:
Is it possible to write a validation rule for a field based on another
field? I have a field [CertType] that only gets filled in if the
[CourseStatus] field is filled in with a P otherwise it stays null. Help
would be greatly appreciated.
Click to expand...
Click to expand...
 
Try:
([CourseStatus]="P" And [CourseStatus] Is Not Null) Xor ([CertType] Is Null)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
djhsmith23 said:
Allen,
I did as you suggested. It works great if anything but P is entered in
CourseStatus but if P is entered it allows CertType to be null also. Is
there a way that it would also require that a value be inputted in CertType
if CourseStatus is P?

Thanks,
Donna

Allen Browne said:
Open your table in design view.
Open the Properties box (View menu).
Enter a Validation Rule in the Properties box. This is the rule for the
table - different to the Validation Rule in the lower pane of table design,
which applies to a field.

To insist that CertType can have a value only if CourseStatus is P, enter
something like this:

([CourseStatus] = "P" AND [CertType] Is Not Null) OR ([CertType Is Null)

DSmith said:
Is it possible to write a validation rule for a field based on another
field? I have a field [CertType] that only gets filled in if the
[CourseStatus] field is filled in with a P otherwise it stays null. Help
would be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Works great. Thanks.

Allen Browne said:
Try:
([CourseStatus]="P" And [CourseStatus] Is Not Null) Xor ([CertType] Is Null)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
djhsmith23 said:
Allen,
I did as you suggested. It works great if anything but P is entered in
CourseStatus but if P is entered it allows CertType to be null also. Is
there a way that it would also require that a value be inputted in CertType
if CourseStatus is P?

Thanks,
Donna

Allen Browne said:
Open your table in design view.
Open the Properties box (View menu).
Enter a Validation Rule in the Properties box. This is the rule for the
table - different to the Validation Rule in the lower pane of table design,
which applies to a field.

To insist that CertType can have a value only if CourseStatus is P, enter
something like this:

([CourseStatus] = "P" AND [CertType] Is Not Null) OR ([CertType Is Null)

Is it possible to write a validation rule for a field based on another
field? I have a field [CertType] that only gets filled in if the
[CourseStatus] field is filled in with a P otherwise it stays null. Help
would be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top