 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / January 2008Validation Rule
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 >I am using Access 97. > I have a table named "Stocks". [quoted text clipped - 11 lines] > 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.  SignatureBrendan 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". [quoted text clipped - 17 lines] > will try to evaluate the literal text between the quotes rather than the > value in the field. 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? SignatureBrendan Reynolds >I inserted this into the Table Properties: > [quoted text clipped - 28 lines] >> will try to evaluate the literal text between the quotes rather than the >> value in the field. 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? > [quoted text clipped - 33 lines] > >> will try to evaluate the literal text between the quotes rather than the > >> value in the field. 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". [quoted text clipped - 17 lines] > will try to evaluate the literal text between the quotes rather than the > value in the field. 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: > [quoted text clipped - 25 lines] > > will try to evaluate the literal text between the quotes rather than the > > value in the field. 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. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (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. [quoted text clipped - 43 lines] >> > the >> > value in the field. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.