Amal Nagm

• Data types Data types typically provide the first layer of validation. When you design a database table, you define a data type for each field in the table, and that data type restricts what users can enter. For example, a Date/Time field accepts only dates and times, a Currency field accepts only monetary data, and so on.
• Field sizes Field sizes provide another layer of validation. For example, if you create a field that stores first names, you can set it to accept a maximum of 20 characters. Doing so can prevent a malicious user from pasting in large amounts of gibberish text into the field, or it can prevent an inexperienced user from mistakenly entering a first and last name in a field designed only to hold a first name.
• Table properties Table properties provide very specific types of validation. For example, you can set the Required property to Yes and, as a result, force users to enter a value in a field. You can also use the Validation Rule property to require specific values, and the Validation Text property to alert your users to any mistakes. For example, entering a rule such as >100 And <1000 in the Validation Rule property forces users to enter values between 100 and 1,000. A rule such as [EndDate]>=[StartDate] forces users to enter an ending date that occurs on or after a starting date. Entering text such as "Enter values between 100 and 1,000" or "Enter an ending date on or after the start date" in the Validation Text property tells users when they have made a mistake and how to fix the error.
  
• Input masks You can use an input mask to validate data by forcing users to enter values in a specific way. For example, an input mask can force users to enter dates in a European format, such as 2007.04.14.

Types of validation rules

• Field validation rules Use a field validation rule to check the value that you enter in a field when you leave the field. For example, suppose you have a Date field, and you enter >=#01/01/2007# in the Validation Rule property of that field. Your rule now requires users to enter dates on or after January 1, 2007. If you enter a date earlier than 2007 and then try to place the focus on another field, Access prevents you from leaving the current field until you fix the problem.
• Record (or table) validation rules Use a record validation rule to control when you can save a record (a row in a table). Unlike field validation rules, record validation rules refer to other fields in the same table. You create record validation rules when you need to check the values in one field against the values in another. For example, suppose your business requires you to ship products within 30 days and, if you don't ship within that time, you must refund part of the purchase price to your customer. You can define a record validation rule such as [RequiredDate]<=[OrderDate]+30 to ensure that someone doesn't enter a ship date (the value in the RequiredDate field) too far into the future.

Where you can use validation rules

What you can put in a validation rule

Validate data during entry in table fields

Validate data in a field

1. In the Navigation Pane, right-click the table that you want to change, and then click Design View.
2. In the Field Name column, select the field that you want to change.
3. In the lower section of the table designer, on the General tab, select the Validation Rule property box, and then enter your validation rule. -or-
   Click On the Data tab of the property sheet, click next to start the Expression Builder and create your expression.
   
   Enter a rule that applies only to the field. For example, you can enter >0 to force users to enter positive values. Keep in mind that a validation rule for a field does not reference other fields in the table. If the rule does reference other fields, you are creating record-level validation.
   
4. Select the Validation Text property box and enter a validation message. The message you enter depends on your validation rule. Keep the message short and try to explain where the user is going wrong. To continue the example from the previous step, you could use Enter only positive numbers as the validation text.
   
5. Save your work.

Validate data in a record

1. Repeat steps 1-2 in the previous section to open a table in Design view.
2. On the General tab, enter a record-level rule in the Validation Rule property box. -or-
   Click next to start the Expression Builder and create your expression.
   A record-level validation rule references more than one table field. For example, a rule such as [RequiredDate]<=[OrderDate]+30 references two table fields, RequiredDate and OrderDate, and it ensures that users enter ship dates that occur no later than 30 days after an order is entered. For more examples of record-level validation
   
3. Save your changes.

Test your validation rules

1. Open the table that contains your validation rule in Design View.
2. On the Design tab, in the Tools group, click Test Validation Rules.
3. Click Yes to close the alert message and start the test.
4. If prompted to save your table, Click Yes.
5. You might see a variety of other alert messages as you proceed. Read the instructions in each message, and then click Yes or No, as appropriate, to complete or stop the testing.

Test validation rules by using a query

1. On the Create tab, in the Other group, click Query Design.
   Access opens a new query in Design view, and displays the Show Table dialog box.
   
2. In the Show Table dialog box, select the table or tables that you want to use in your query, click Add to add them to the query, and then click Close. The selected tables appear as windows in the upper section of the query designer.
   
3. In each table, double-click the fields that you want to include in your query. -or-
   Drag the fields from the table and drop them on a blank cell in the Field row in the lower section of the design grid.
   Make sure that you add the field that contains your validation rule.
   
4. In the Criteria cell of the field that contains your validation rule, enter the opposite of that rule. For example, if you use BETWEEN 100 AND 1000, enter <100 OR >1000.
   
5. On the Design tab, in the Results group, click Run.

Validate data during entry in forms

Create a validation rule

1. In the Navigation Pane, right-click the form that you want to change, and then click Design View.
2. Right-click the control that you want to change, and then click Properties to open the property sheet for the control.
3. Click the All tab, and then enter your validation rule in the Validation Rule property box. -or-
   Click next to start the Expression Builder and create an expression.
   
   
4. Enter a message in the Validation Text property box.
5. Save your changes.

Lock a control

1. Follow steps 1-2 in the previous section to open the property sheet for the control that you want to lock.
2. Click the All tab, locate the Enabled and Locked property boxes, and then do one of the following:
   • To disable the control (make the control appear dimmed and unable to receive focus), set the Enabled property to No.
   • To make the data in the control readable, but not allow users to change the data, set the Locked property to Yes. If you set the Enabled property to No and the Locked property to Yes, the control won't appear dimmed, but it won't be able to receive focus.

Validate data during import operations

Validation rule reference

Validation rule examples

Syntax for common validation rules

• Surround the names of table fields with square brackets, like so: [RequiredDate]<=[OrderDate]+30.
• Surround dates with pound signs (#), like so: <#01/01/2007#
• Surround text values with double quotation marks, like so: IN ("Tokyo","Paris","Moscow"). Also, note that you separate items with commas, and you place lists inside parentheses.

Using wildcard characters in validation rules