Hi all,

I had to set a validation rule to affect 3 fields the other day. I have not done this for years, and "surprised" myself that I could recall and apply it. As record validation is often overlooked I thought I'd just bring it to your attention again and certainly recommend it. If a user does not know about this feature, they are often diverted to creating a more advanced macro to test for inaccuracies across the record. This is not necessary.

Field Validation:
This is where a user can supply a rule which affects ONLY the field in which the value is being typed. For example:
1. The discount field cannot have a discount rate of greater than 20%. The rule can be set by selecting the Discount field in the table design view and typing <=0.20 into the Validation rule textbox in the field properties area.
2. The software medium can only be CD-ROM or Diskette. The rule to set would be "CD-ROM" Or "Diskette"
Its a good habit to add Validation Text to inform the user with a message of invalid entry.

Record Validation
Record Validation differs to Field Validation in that a user can set a rule across multiple fields within the record. If the rule (in the form of an expression) is violated the entire record is disallowed. This often overlooked validation option is accessed by switching the table into design mode and clicking the Properties Button on the design toolbar, or right-clicking in the field area in design view and choosing Properties. When the dialog opens you will notice a Validation Rule and Validation Text option. Examples of rules you can enter here are:
1. Current stock value and On Order values may not exceed 50. The rule would look like this: [InStock]+[OnOrder]<=50. If the added value of these two fields exceeds 50, the record will not be written to the table and your Validation Text message will be displayed.
2. If the Ship Date of an order must be less than 30 days after the Order date, a rule can be set up as follows: [ShipDate] - [OrderDate]<30 Or: =DateDiff([OrderDate],[ShipDate],"d")<30.

Hope this info will benefit you!