You can vet or validate data in Access desktop databases as you enter it by using validation rules. You can use the expression builder to help you format the rule correctly. Validation rules can be set in either table design or table datasheet view. There are three types of validation rules in Access:
1. Field Validation Rule You can use a field validation rule to specify a criterion that all valid field values must meet. You should not have to specify the current field as a part of the rule unless you are using the field in a function. Restrictions on types of characters to be entered in a field may be easier to do with an Input Mask Links to an external site.. For example, a date field might have a validation rule that disallows values in the past.
Quick examples:
Disallow date values in the past: >=Date()
Generally accepted email format: Is Null OR ((Like "*?@?*.?*") AND (Not Like "*[ ,;]*"))
Number less than or qual to five: <=5
Currency field can't be negative: >=0
Restrict character length in string: Len([StringFieldName])<100
2. Record Validation Rule You can use a record validation rule to specify a condition that all valid records must satisfy. You can compare values across different fields using a record validation rule. For example, a record with two date fields might require that values of one field always precede values of the other field (e.g., StartDate is before EndDate).
Quick examples:
Ensure the end date doesn't come before the start date: [End Date]>=[Start Date]
Enter a required date that occurs no more than 30 days after the order date: [RequiredDate]<=[OrderDate]+30
3. Validation on a form You can use the Validation Rule property of a control on a form to specify a criterion that all values input to that control must meet. The Validation Rule control property works like a field validation rule. Typically, you use a form validation rule instead of a field validation rule if the rule was specific only to that form and not to the table no matter where it was used.
Require a value in a field
There are two basic ways that you can require a value in a field in an Access table:
-
Set the field's Required property to Yes You can do this by opening the table in Design view. This method is easy and a good choice if you only want to change one field one time.
-
Use a data-definition query that creates a non-null index on the field You can do this by using SQL view. This method is not as easy as using Design view, but has an advantage: you can save the data-definition query and use it again later. This is useful if you periodically delete and re-create tables and want to require values for some of the fields.
Set the Required property for a field to Yes
-
In the Navigation Pane, right-click the table and then click Design View.
-
Select the field that you want to require always has a value.
-
In the Field Properties pane, on the General tab, set the Required property to Yes.
Control Data Entry Formats with Input Masks
You can help people enter data correctly into your Access desktop database by providing input masks for fields that contain data that is always formatted a certain way. For example, you can use an input mask to make sure that people enter correctly formatted phone numbers into a phone number field.
An input mask only affects whether Access accepts the data – the mask does not change how the data is stored, which is controlled by the field’s data type and other properties.
Create or delete a lookup field
Creating a lookup field not only improves the meaning of data but helps avoid data entry errors by limiting the values that can be entered. A lookup field can display a user friendly value that is bound to another value in the source data table. For example, you want to record the customer for an order in the Orders table. However, all customer information is tracked in the Customers table. You can create a lookup field that displays the customer information in a combo box or list box control. Then, when you select the customer in that control, an appropriate value — such as the customer's primary key value — is stored in the order record.