Validation Rule for field using table property

[Guest]

Hello,

I need to set a validation rule for one field based upon value on in a
second field. Typically when I need to validate one field based upon value
in another field I do so at the form level using vba. I but would like to
use simpler method at the table level if possible.

The details for the specific rule are:

if fieldA > 1, then fieldB must be >0.

If this validation is indeed something that can only be set at the form
level and/or only using vba please let me know.

Thank you so much for help with this basic question.

 

[John W. Vinson]

Hello,

I need to set a validation rule for one field based upon value on in a
second field. Typically when I need to validate one field based upon value
in another field I do so at the form level using vba. I but would like to
use simpler method at the table level if possible.

The details for the specific rule are:

if fieldA > 1, then fieldB must be >0.

If this validation is indeed something that can only be set at the form
level and/or only using vba please let me know.

Thank you so much for help with this basic question.

A Field Validation rule cannot reference another field in the table (the
logic, I'd guess, is that you cannot control the order in which data is
entered for the fields, so FieldA might not have any value at all at the time
FieldB is set).

But you can view the Table's properties and put an expression in the Table
Validation rule:

(FieldA > 1 AND FieldB > 0) OR (FieldA <= 1 OR IS NULL)

You need to cover all the logical combinations, I can't tell if the second
half of this expression meets your needs.

John W. Vinson [MVP]

 

[Guest]

Hello John,
First I must say that I have been reading, although not posting questions,
to forums for sometime and feel that I have gotten to know you as well as
other MVPs if only by name.
Thank you John and to all who contribute to the forum.

The explanation is perfect and the reminder that I need to cover all logical
combinations is especially appreciated.
Here are the combinations:
Fields individually:
FieldA must be >=1 and not null
FieldB must be >= 0 and not null.

FieldA and FieldB in combination:
If FieldA = 1, then FieldB must = 0
If fieldA =>2, then FieldB must be >= 1.
If FieldB >=1 then field A must be >=1

So the fields would be correct if they appear as:
FieldA FieldB
1 0
2+ 1+


I believe I have covered the possibility of null values in FieldA and FieldB
by setting the properties at the field level as follows:

FieldA
The properties for fieldA (datatype Integer) are:
default value: 1
validation rule: <>0 And Is Not Null
validation text Field A must be greater than >=1.

The properties for FieldB (datatype currency) are:
Default value = 0
validation rule - is not null

I tested the field validation vs the table validation and it does appear
that access will fire the field validation before the record level
validation. So I think I am covered for null values for each field and zero
values for fieldA. Please let me know if my thinking is incorrect?

I am having trouble with applying logic for the fields in combination. What
I have got thus far is:
([fieldA]>1 And [fldB]>=0) Or ([FieldA]>=2 Or [FieldB]<1)

If you would please help. I am afraid I am just lost in the boolean logic.
For some reason it is alway easier for me to think in terms of select case
and if then statements. If this makes sense

Thank you !

 

[Jeff Boyce]

Given the limitation for using the User Interface to set field-to-field
validation at the table level, why have you decided that you MUST do this in
the table rather than (as you suggested as more appropriate) in a form?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello John,
First I must say that I have been reading, although not posting questions,
to forums for sometime and feel that I have gotten to know you as well as
other MVPs if only by name.
Thank you John and to all who contribute to the forum.

The explanation is perfect and the reminder that I need to cover all
logical combinations is especially appreciated.
Here are the combinations:
Fields individually:
FieldA must be >=1 and not null
FieldB must be >= 0 and not null.

FieldA and FieldB in combination:
If FieldA = 1, then FieldB must = 0
If fieldA =>2, then FieldB must be >= 1.
If FieldB >=1 then field A must be >=1

So the fields would be correct if they appear as:
FieldA FieldB
1 0
2+ 1+


I believe I have covered the possibility of null values in FieldA and
FieldB by setting the properties at the field level as follows:

FieldA
The properties for fieldA (datatype Integer) are:
default value: 1
validation rule: <>0 And Is Not Null
validation text Field A must be greater than >=1.

The properties for FieldB (datatype currency) are:
Default value = 0
validation rule - is not null

I tested the field validation vs the table validation and it does appear
that access will fire the field validation before the record level
validation. So I think I am covered for null values for each field and
zero values for fieldA. Please let me know if my thinking is incorrect?

I am having trouble with applying logic for the fields in combination.
What I have got thus far is:
([fieldA]>1 And [fldB]>=0) Or ([FieldA]>=2 Or [FieldB]<1)

If you would please help. I am afraid I am just lost in the boolean
logic. For some reason it is alway easier for me to think in terms of
select case and if then statements. If this makes sense

Thank you !

A Field Validation rule cannot reference another field in the table (the
logic, I'd guess, is that you cannot control the order in which data is
entered for the fields, so FieldA might not have any value at all at the
time
FieldB is set).

But you can view the Table's properties and put an expression in the
Table
Validation rule:

(FieldA > 1 AND FieldB > 0) OR (FieldA <= 1 OR IS NULL)

You need to cover all the logical combinations, I can't tell if the
second
half of this expression meets your needs.

John W. Vinson [MVP]

 

[Guest]

Hi Jeff,

I actually thought that it might be more expedient to set it at the table
level. Sounds like it is best handled at the form level.

Thank you

Given the limitation for using the User Interface to set field-to-field
validation at the table level, why have you decided that you MUST do this
in the table rather than (as you suggested as more appropriate) in a form?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello John,
First I must say that I have been reading, although not posting
questions, to forums for sometime and feel that I have gotten to know you
as well as other MVPs if only by name.
Thank you John and to all who contribute to the forum.

The explanation is perfect and the reminder that I need to cover all
logical combinations is especially appreciated.
Here are the combinations:
Fields individually:
FieldA must be >=1 and not null
FieldB must be >= 0 and not null.

FieldA and FieldB in combination:
If FieldA = 1, then FieldB must = 0
If fieldA =>2, then FieldB must be >= 1.
If FieldB >=1 then field A must be >=1

So the fields would be correct if they appear as:
FieldA FieldB
1 0
2+ 1+


I believe I have covered the possibility of null values in FieldA and
FieldB by setting the properties at the field level as follows:

FieldA
The properties for fieldA (datatype Integer) are:
default value: 1
validation rule: <>0 And Is Not Null
validation text Field A must be greater than >=1.

The properties for FieldB (datatype currency) are:
Default value = 0
validation rule - is not null

I tested the field validation vs the table validation and it does appear
that access will fire the field validation before the record level
validation. So I think I am covered for null values for each field and
zero values for fieldA. Please let me know if my thinking is incorrect?

I am having trouble with applying logic for the fields in combination.
What I have got thus far is:
([fieldA]>1 And [fldB]>=0) Or ([FieldA]>=2 Or [FieldB]<1)

If you would please help. I am afraid I am just lost in the boolean
logic. For some reason it is alway easier for me to think in terms of
select case and if then statements. If this makes sense

Thank you !

Hello,

I need to set a validation rule for one field based upon value on in a
second field. Typically when I need to validate one field based upon
value
in another field I do so at the form level using vba. I but would like
to
use simpler method at the table level if possible.

The details for the specific rule are:

if fieldA > 1, then fieldB must be >0.

If this validation is indeed something that can only be set at the form
level and/or only using vba please let me know.

Thank you so much for help with this basic question.



A Field Validation rule cannot reference another field in the table (the
logic, I'd guess, is that you cannot control the order in which data is
entered for the fields, so FieldA might not have any value at all at the
time
FieldB is set).

But you can view the Table's properties and put an expression in the
Table
Validation rule:

(FieldA > 1 AND FieldB > 0) OR (FieldA <= 1 OR IS NULL)

You need to cover all the logical combinations, I can't tell if the
second
half of this expression meets your needs.

John W. Vinson [MVP]

 

[Jamie Collins]

FieldA must be >=1 and not null
FieldB must be >= 0 and not null.

FieldA and FieldB in combination:
If FieldA = 1, then FieldB must = 0
If fieldA =>2, then FieldB must be >= 1.
If FieldB >=1 then field A must be >=1

So the fields would be correct if they appear as:
FieldA FieldB
1 0
2+ 1+

It's a good idea to do validation in both the database and the front
end.

For the table (row-level) validation rule, try:

(FieldA = 1 AND FieldB = 0) OR (FieldA >= 2 AND FieldB >= 1)

Jamie.

--

 

[Jeff Boyce]

Hopefully you've seen that there is no "right" answer.

Some folks (I plead no contest) figure if it works, fine... and if I have
more time, I'll try to make it "elegant".

I distinguish between these two approaches by figuring if the users are
satisfied that the application is doing what it needs to, in a timely
fashion, without undue strain on them, then it works. ... and if the
developers/programmers/theoreticians say "wow!", it's "elegant".

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff,

I actually thought that it might be more expedient to set it at the table
level. Sounds like it is best handled at the form level.

Thank you

Given the limitation for using the User Interface to set field-to-field
validation at the table level, why have you decided that you MUST do this
in the table rather than (as you suggested as more appropriate) in a
form?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello John,
First I must say that I have been reading, although not posting
questions, to forums for sometime and feel that I have gotten to know
you as well as other MVPs if only by name.
Thank you John and to all who contribute to the forum.

The explanation is perfect and the reminder that I need to cover all
logical combinations is especially appreciated.
Here are the combinations:
Fields individually:
FieldA must be >=1 and not null
FieldB must be >= 0 and not null.

FieldA and FieldB in combination:
If FieldA = 1, then FieldB must = 0
If fieldA =>2, then FieldB must be >= 1.
If FieldB >=1 then field A must be >=1

So the fields would be correct if they appear as:
FieldA FieldB
1 0
2+ 1+


I believe I have covered the possibility of null values in FieldA and
FieldB by setting the properties at the field level as follows:

FieldA
The properties for fieldA (datatype Integer) are:
default value: 1
validation rule: <>0 And Is Not Null
validation text Field A must be greater than >=1.

The properties for FieldB (datatype currency) are:
Default value = 0
validation rule - is not null

I tested the field validation vs the table validation and it does appear
that access will fire the field validation before the record level
validation. So I think I am covered for null values for each field and
zero values for fieldA. Please let me know if my thinking is incorrect?

I am having trouble with applying logic for the fields in combination.
What I have got thus far is:
([fieldA]>1 And [fldB]>=0) Or ([FieldA]>=2 Or [FieldB]<1)

If you would please help. I am afraid I am just lost in the boolean
logic. For some reason it is alway easier for me to think in terms of
select case and if then statements. If this makes sense

Thank you !





Hello,

I need to set a validation rule for one field based upon value on in a
second field. Typically when I need to validate one field based upon
value
in another field I do so at the form level using vba. I but would like
to
use simpler method at the table level if possible.

The details for the specific rule are:

if fieldA > 1, then fieldB must be >0.

If this validation is indeed something that can only be set at the form
level and/or only using vba please let me know.

Thank you so much for help with this basic question.



A Field Validation rule cannot reference another field in the table
(the
logic, I'd guess, is that you cannot control the order in which data is
entered for the fields, so FieldA might not have any value at all at
the time
FieldB is set).

But you can view the Table's properties and put an expression in the
Table
Validation rule:

(FieldA > 1 AND FieldB > 0) OR (FieldA <= 1 OR IS NULL)

You need to cover all the logical combinations, I can't tell if the
second
half of this expression meets your needs.

John W. Vinson [MVP]

 

[Guest]

I also plead not contest if it works, fine and in this case at the very
early stages, so thought I would try for what I thought would be simplier
methods. The many methods of accomplishing one task is what keeps things
interesting.
Thank you

Hopefully you've seen that there is no "right" answer.

Some folks (I plead no contest) figure if it works, fine... and if I have
more time, I'll try to make it "elegant".

I distinguish between these two approaches by figuring if the users are
satisfied that the application is doing what it needs to, in a timely
fashion, without undue strain on them, then it works. ... and if the
developers/programmers/theoreticians say "wow!", it's "elegant".

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff,

I actually thought that it might be more expedient to set it at the table
level. Sounds like it is best handled at the form level.

Thank you

Given the limitation for using the User Interface to set field-to-field
validation at the table level, why have you decided that you MUST do
this in the table rather than (as you suggested as more appropriate) in
a form?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello John,
First I must say that I have been reading, although not posting
questions, to forums for sometime and feel that I have gotten to know
you as well as other MVPs if only by name.
Thank you John and to all who contribute to the forum.

The explanation is perfect and the reminder that I need to cover all
logical combinations is especially appreciated.
Here are the combinations:
Fields individually:
FieldA must be >=1 and not null
FieldB must be >= 0 and not null.

FieldA and FieldB in combination:
If FieldA = 1, then FieldB must = 0
If fieldA =>2, then FieldB must be >= 1.
If FieldB >=1 then field A must be >=1

So the fields would be correct if they appear as:
FieldA FieldB
1 0
2+ 1+


I believe I have covered the possibility of null values in FieldA and
FieldB by setting the properties at the field level as follows:

FieldA
The properties for fieldA (datatype Integer) are:
default value: 1
validation rule: <>0 And Is Not Null
validation text Field A must be greater than >=1.

The properties for FieldB (datatype currency) are:
Default value = 0
validation rule - is not null

I tested the field validation vs the table validation and it does
appear that access will fire the field validation before the record
level validation. So I think I am covered for null values for each
field and zero values for fieldA. Please let me know if my thinking is
incorrect?

I am having trouble with applying logic for the fields in combination.
What I have got thus far is:
([fieldA]>1 And [fldB]>=0) Or ([FieldA]>=2 Or [FieldB]<1)

If you would please help. I am afraid I am just lost in the boolean
logic. For some reason it is alway easier for me to think in terms of
select case and if then statements. If this makes sense

Thank you !





Hello,

I need to set a validation rule for one field based upon value on in a
second field. Typically when I need to validate one field based upon
value
in another field I do so at the form level using vba. I but would like
to
use simpler method at the table level if possible.

The details for the specific rule are:

if fieldA > 1, then fieldB must be >0.

If this validation is indeed something that can only be set at the
form
level and/or only using vba please let me know.

Thank you so much for help with this basic question.



A Field Validation rule cannot reference another field in the table
(the
logic, I'd guess, is that you cannot control the order in which data
is
entered for the fields, so FieldA might not have any value at all at
the time
FieldB is set).

But you can view the Table's properties and put an expression in the
Table
Validation rule:

(FieldA > 1 AND FieldB > 0) OR (FieldA <= 1 OR IS NULL)

You need to cover all the logical combinations, I can't tell if the
second
half of this expression meets your needs.

John W. Vinson [MVP]

 

[Guest]

Logic is working perfectly. Thank you!

It's a good idea to do validation in both the database and the front
end.

For the table (row-level) validation rule, try:

(FieldA = 1 AND FieldB = 0) OR (FieldA >= 2 AND FieldB >= 1)

Jamie.

 

[Jamie Collins]

Hopefully you've seen that there is no "right" answer.

I plead no contest

Let's look for a win:win. I say, if you are going to do validation in
just one place then do it as close to the data storage as possible
e.g. a validation rule or CHECK constraint at the engine level as a
'catch all' (e.g. to guard against bugs introduced into front end
code, SQL executed directly, etc). But I also say, why do it in only
one place? Better to have validation in both the engine and the front
end(s).

Jamie.

--