Field Validation

[MVP - WannaB]

Hello, and thank you for your time.
I am adding a new field to a table and I've been requested to ensure that it
not be left blank on the condition that another field in the same table has
been filled.
So my question is
What is the best way to enforce a rule to require that a value be entered in
a field, based on the presence of data in another field. Should this be done
at that TABLE properties OR is it better to do this using VBA on the form?
There is only one form where this data will be entered.
AND please provide an example.
THANKS AGAIN..

 

[Dennis]

In the forms before update event you can check your fields and if they do not
match your conditions set
Cancel = True

 

[Allen Browne]

If the rule is absolute (no case where you need to override it), it's best
to put the rule in the table. That way the engine always enforces the rule
(e.g. even if you run an append or update query.)

Use the rule for the table (not field), i.e. the one in the Properties box
in table design. The rule will be something like this:
([Field1] Is Null) OR ([Field2] Is Not Null)

Explanation (and examples of other rules):
http://allenbrowne.com/ValidationRule.html

 

[MVP - WannaB]

Thanks Allen, that is very helpful, but as I look at this more closely I am
finding that it is more difficult that I originally thought...
I need to apply the same condition to 2 fields, and this condition is based
on the value (data) entered in 2 other fields, all in the same table and same
record.
SOMETHING LIKE (IF FIELD1 = "BBB" AND FIELD2 = "CCC" THEN FIELD3 AND
FIELD4 CAN NOT BE NULL)
Is there a way that I can use IIF() in this validation? I can not find
anything like that in your linked site, or anywhere else... and I see
nothing about setting validation rules for more then one field in TABLE
PROPERTIES.
Is table properties validation still the right place for this or need I do
this elsewhere?
I appreciate you help

If the rule is absolute (no case where you need to override it), it's best
to put the rule in the table. That way the engine always enforces the rule
(e.g. even if you run an append or update query.)

Use the rule for the table (not field), i.e. the one in the Properties box
in table design. The rule will be something like this:
([Field1] Is Null) OR ([Field2] Is Not Null)

Explanation (and examples of other rules):
http://allenbrowne.com/ValidationRule.html

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

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

Hello, and thank you for your time.
I am adding a new field to a table and I've been requested to ensure that
it
not be left blank on the condition that another field in the same table
has
been filled.
So my question is
What is the best way to enforce a rule to require that a value be entered
in
a field, based on the presence of data in another field. Should this be
done
at that TABLE properties OR is it better to do this using VBA on the form?
There is only one form where this data will be entered.
AND please provide an example.
THANKS AGAIN..

 

[Allen Browne]

Perhaps:
([Field1] Is Null) OR ([Field2] Is Null)
OR ([Field1] <> "BBB") OR ([Field2] <> "CCC")
OR ([Field4] Is Not Null)

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

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

Thanks Allen, that is very helpful, but as I look at this more closely I
am
finding that it is more difficult that I originally thought...
I need to apply the same condition to 2 fields, and this condition is
based
on the value (data) entered in 2 other fields, all in the same table and
same
record.
SOMETHING LIKE (IF FIELD1 = "BBB" AND FIELD2 = "CCC" THEN FIELD3 AND
FIELD4 CAN NOT BE NULL)
Is there a way that I can use IIF() in this validation? I can not find
anything like that in your linked site, or anywhere else... and I see
nothing about setting validation rules for more then one field in TABLE
PROPERTIES.
Is table properties validation still the right place for this or need I do
this elsewhere?
I appreciate you help

If the rule is absolute (no case where you need to override it), it's
best
to put the rule in the table. That way the engine always enforces the
rule
(e.g. even if you run an append or update query.)

Use the rule for the table (not field), i.e. the one in the Properties
box
in table design. The rule will be something like this:
([Field1] Is Null) OR ([Field2] Is Not Null)

Explanation (and examples of other rules):
http://allenbrowne.com/ValidationRule.html

Hello, and thank you for your time.
I am adding a new field to a table and I've been requested to ensure
that
it
not be left blank on the condition that another field in the same table
has
been filled.
So my question is
What is the best way to enforce a rule to require that a value be
entered
in
a field, based on the presence of data in another field. Should this
be
done
at that TABLE properties OR is it better to do this using VBA on the
form?
There is only one form where this data will be entered.
AND please provide an example.

 

[MVP - WannaB]

Are you saying to use that in the table properties validation rule?

Perhaps:
([Field1] Is Null) OR ([Field2] Is Null)
OR ([Field1] <> "BBB") OR ([Field2] <> "CCC")
OR ([Field4] Is Not Null)

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

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

Thanks Allen, that is very helpful, but as I look at this more closely I
am
finding that it is more difficult that I originally thought...
I need to apply the same condition to 2 fields, and this condition is
based
on the value (data) entered in 2 other fields, all in the same table and
same
record.
SOMETHING LIKE (IF FIELD1 = "BBB" AND FIELD2 = "CCC" THEN FIELD3 AND
FIELD4 CAN NOT BE NULL)
Is there a way that I can use IIF() in this validation? I can not find
anything like that in your linked site, or anywhere else... and I see
nothing about setting validation rules for more then one field in TABLE
PROPERTIES.
Is table properties validation still the right place for this or need I do
this elsewhere?
I appreciate you help

If the rule is absolute (no case where you need to override it), it's
best
to put the rule in the table. That way the engine always enforces the
rule
(e.g. even if you run an append or update query.)

Use the rule for the table (not field), i.e. the one in the Properties
box
in table design. The rule will be something like this:
([Field1] Is Null) OR ([Field2] Is Not Null)

Explanation (and examples of other rules):
http://allenbrowne.com/ValidationRule.html

Hello, and thank you for your time.
I am adding a new field to a table and I've been requested to ensure
that
it
not be left blank on the condition that another field in the same table
has
been filled.
So my question is
What is the best way to enforce a rule to require that a value be
entered
in
a field, based on the presence of data in another field. Should this
be
done
at that TABLE properties OR is it better to do this using VBA on the
form?
There is only one form where this data will be entered.
AND please provide an example.

 

[Allen Browne]

Are you saying to use that in the table properties validation rule?

Do you think you might try that for yourself?
(Time to move on.)