Validation Rule

[Henry]

I am using Access 97.
I have a table named "Stocks".
This table has 2 fields named "LongShort" (Text) and "Shares" (Number).
The field LongShort can receive either "L" or "S".
I wish to force the entry of a positive number into Shares if LongShort
contains "L".
I wish to force the entry of a negative number into Shares if LongShort
contains "S".
I have entered this Validation Rule into the field "Shares":
("LongShort"='S' And "Shares"<0) Or ("LongShort"='L' And "Shares">=0)
It does not work. In fact, it prevents me from entering any value into Shares.
Can anyone help?
Thank you,
Henry

 

[Brendan Reynolds]

I am using Access 97.
I have a table named "Stocks".
This table has 2 fields named "LongShort" (Text) and "Shares" (Number).
The field LongShort can receive either "L" or "S".
I wish to force the entry of a positive number into Shares if LongShort
contains "L".
I wish to force the entry of a negative number into Shares if LongShort
contains "S".
I have entered this Validation Rule into the field "Shares":
("LongShort"='S' And "Shares"<0) Or ("LongShort"='L' And "Shares">=0)
It does not work. In fact, it prevents me from entering any value into
Shares.
Can anyone help?
Thank you,
Henry

Field validation rules can't refer to other fields, for that you need a
table validation rule (look for it in the properties of the table). You also
need to remove the quotes around the field names, otherwise the expression
will try to evaluate the literal text between the quotes rather than the
value in the field.

 

[Henry]

I inserted this into the Table Properties:

([LongShort]="S" And [Shares]<0) Or ([LongShort]="L" And [Shares]>0)

But when I use a Form to enter data into the table, this rule has no effect.

Henry

 

[Henry]

I inserted this into the Table Properties:

([LongShort]="S" And [Shares]<0) Or ([LongShort]="L" And [Shares]>0)

But when I use a Form to enter data into the table, this rule has no effect.

Henry

 

[Brendan Reynolds]

1) Where exactly in the tables properties did you enter the expression?

2) What happens if you try to enter data that violates the rule directly
into the table, not via the form?


--
Brendan Reynolds

I inserted this into the Table Properties:

([LongShort]="S" And [Shares]<0) Or ([LongShort]="L" And [Shares]>0)

But when I use a Form to enter data into the table, this rule has no
effect.

Henry

Field validation rules can't refer to other fields, for that you need a
table validation rule (look for it in the properties of the table). You
also
need to remove the quotes around the field names, otherwise the
expression
will try to evaluate the literal text between the quotes rather than the
value in the field.

 

[Henry]

I entered it as follows:

I went to the Stocks Table using Design. I clicked on View / Properties.
I clicked on Validation Rule and entered the Rule into the Expression

Builder. I then saved the Table.

I can enter the wrong data directly into the Table but it only indicates a
rejection when I click on the left-most column.

I was hoping that, using a Form, I would get an immediate error message when
I entered the wrong value into Shares.

Henry

1) Where exactly in the tables properties did you enter the expression?

2) What happens if you try to enter data that violates the rule directly
into the table, not via the form?


--
Brendan Reynolds

I inserted this into the Table Properties:

([LongShort]="S" And [Shares]<0) Or ([LongShort]="L" And [Shares]>0)

But when I use a Form to enter data into the table, this rule has no
effect.

Henry

I am using Access 97.
I have a table named "Stocks".
This table has 2 fields named "LongShort" (Text) and "Shares" (Number).
The field LongShort can receive either "L" or "S".
I wish to force the entry of a positive number into Shares if LongShort
contains "L".
I wish to force the entry of a negative number into Shares if LongShort
contains "S".
I have entered this Validation Rule into the field "Shares":
("LongShort"='S' And "Shares"<0) Or ("LongShort"='L' And "Shares">=0)
It does not work. In fact, it prevents me from entering any value into
Shares.
Can anyone help?
Thank you,
Henry


Field validation rules can't refer to other fields, for that you need a
table validation rule (look for it in the properties of the table). You
also
need to remove the quotes around the field names, otherwise the
expression
will try to evaluate the literal text between the quotes rather than the
value in the field.

 

[Henry]

I think I have been asking the wrong question.
When I enter bad data into the Form nothing happens until I save the Form.
Then i get the message telling me that the Stocks entry is bad.

I was expecting to be told the entry was bad immediately after leaving the
Field.
I guess it does not work that way?

Henry

I inserted this into the Table Properties:

([LongShort]="S" And [Shares]<0) Or ([LongShort]="L" And [Shares]>0)

But when I use a Form to enter data into the table, this rule has no effect.

Henry

Field validation rules can't refer to other fields, for that you need a
table validation rule (look for it in the properties of the table). You also
need to remove the quotes around the field names, otherwise the expression
will try to evaluate the literal text between the quotes rather than the
value in the field.

 

[Douglas J. Steele]

Access only attempts to write the data when focus leaves the row, so that's
the only time table or form level rules get evaluated.

To get it to perform the way you're describing, put logic in the control's
BeforeUpdate event, and set Cancel = True inside that sub if the
condition(s) are violated.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I think I have been asking the wrong question.
When I enter bad data into the Form nothing happens until I save the Form.
Then i get the message telling me that the Stocks entry is bad.

I was expecting to be told the entry was bad immediately after leaving the
Field.
I guess it does not work that way?

Henry

I inserted this into the Table Properties:

([LongShort]="S" And [Shares]<0) Or ([LongShort]="L" And [Shares]>0)

But when I use a Form to enter data into the table, this rule has no
effect.

Henry

I am using Access 97.
I have a table named "Stocks".
This table has 2 fields named "LongShort" (Text) and "Shares"
(Number).
The field LongShort can receive either "L" or "S".
I wish to force the entry of a positive number into Shares if
LongShort
contains "L".
I wish to force the entry of a negative number into Shares if
LongShort
contains "S".
I have entered this Validation Rule into the field "Shares":
("LongShort"='S' And "Shares"<0) Or ("LongShort"='L' And "Shares">=0)
It does not work. In fact, it prevents me from entering any value
into
Shares.
Can anyone help?
Thank you,
Henry


Field validation rules can't refer to other fields, for that you need a
table validation rule (look for it in the properties of the table). You
also
need to remove the quotes around the field names, otherwise the
expression
will try to evaluate the literal text between the quotes rather than
the
value in the field.