The most straightforward and arguably the most essential rules used in data validation are rules that ensure data integrity. Spell check? Data validation. Minimum password length? Every organization will have its own unique rules for how data should be stored and maintained. Setting basic data validation rules will help your company uphold organized standards that will effectively make working with data more efficient. Some other common examples of data validation rules that help maintain integrity and clarity include:.
Validating the structure of data is just as important as validating the data itself. Doing so will ensure that you are using the appropriate data model for the formats that are compatible with the applications you would like to use data in. File formats and their standards are maintained by non-profit organizations, government departments, industry advisory panels, and private companies. With their assistance, they help to continuously develop, document, and define file structures that hold data.
When validating data, the standards and structure of the data model that the dataset is stored in should be well understood. Failing to do so may result in files that are incompatible with applications and other datasets with which you may want to integrate that data. Depending on your fluency in coding languages, writing a script may be an option for validating data. If you had an address book, for example, you might know the person's address and not their phone number, or vice-versa, so it wouldn't make sense to make those fields mandatory.
On the other hand, it doesn't make sense to have an address book entry with no name, so you should check for the presence of the name. Some database programs allow you to check whether the contents of a particular field are unique. This might be useful to prevent users entering the same information twice. For example, if you were creating a car database, you should make the registration number field unique as no two cars should have the same one. If you're using a number field, then you might want to limit the range of inputs.
For example, you might want to limit prices in a stock database so that they are all positive, or limit the range of a percentage field so that the values entered are between 0 and The last one or two digits in a code are used to check the other digits are correct.
Bar code readers in supermarkets use check digits. Format check. Checks the data is in the right format. Length check. Checks the data isn't too short or too long. A password which needs to be six letters long. Test existing data against a new validation rule. Add a validation rule to a control on a form. Validation rule reference. This article explains how to use validation rules and validation text in table fields and form controls.
A validation rule is one way to restrict input in a table field or a control such as a text box on a form. Validation text lets you provide a message to help users who input data that is not valid. When data is entered, Access checks to see whether the input breaks a validation rule — if so, the input is not accepted, and Access displays a message. Data types Every table field has a data type that restricts what users can enter.
Field properties Some field properties restrict data input. For example, the Field Size property of a field restricts input by limiting the amount of data. You can also use the Validation Rule property to require specific values, and the Validation Text property to alert your users to any mistakes.
Entering text such as "Enter values between and 1," 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 You can use these methods of validating data alone or in combination with each other. Data types are not optional, and provide the most basic type of data validation.
For more information about data types, field sizes, and input masks, see the article Introduction to data types and field properties. Field validation rules Use a field validation rule to check the value that you enter in a field when you leave the field. Your rule now requires users to enter dates on or after January 1, If you enter a date earlier than and then try to place the focus on another field, Access prevents you from leaving the current field until you fix the problem.
Record validation rules Use a record validation rule to control when you can save a record a row in a table. Unlike a field validation rule, a record validation rule refers 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. If the syntax for validation rules looks cryptic, see the section What you can put in a validation rule for an explanation of the syntax and some example validation rules. You can define validation rules for table fields and for controls on forms.
When you define rules for tables, those rules apply when you import data. To add validation rules to a table, you open the table and use commands on the Fields tab of the ribbon. To add validation rules to a form, you open the form in Layout view and add rules to the properties of individual controls.
The steps in the section Add a validation rule to a table explain how to add validation rules to table fields. The steps in the section Add a validation rule to a control on a form , later in this article, explain how to add rules to the properties in individual controls.
0コメント