Validating data
When you enter data in an Access table it is validated by data types, for example, you cannot enter text in a numeric field. Further validation is possible by using the validation rule.
Let us examine the following example: The staff in the firm below must live near Pinelands.
Firstly the Field Property Required must be set to 'Yes' to make sure that field is filled in.
Click in the Validation Rule field in the Field Properties area.
Key in the validation rule in the Validation Rule field.
If the rule is too long and will not fit in the box, hold down Shift and press F2, to open the Zoom dialogue box. Key in the Validation Rule.
Note how the rule has changed in the box above. Selected words have quotation marks around them.
In the Validation Text box, add the prompt or error message to help the user add the required data.
In the example below the user has tried to add the town of Claremont. That is not accepted and the prompt indicates that the required town must be near Pinelands.
The information below is correct as it is near Pinelands.
The following Validation Rules and Text will help explain the rule better.
| Validation Rule setting | Validation Text setting |
| <>0 | Enter a zero value |
| 0 Or >100 | Value must be either 0 or over 100 |
| Like "P???" | Value must be four characters beginning with the letter P. |
| <#1/1/99# | Enter a date before 1999 |
| >=#1/1/98# And <#1/1/99# | The date must be in 1998 |
| >=Date() | Date can't be before today's date |
| >=Date()+365 | Date must be at least one year from today |
This work may be freely copied for non-commercial use only. Copyright © Pam Miller