Access Rule Property Data

In Microsoft Access, the "Validation Rule" property is used to define specific criteria that data entered into a table field (or control on a form) must meet. When a user tries to enter data that does not comply with the validation rule, Access will prevent the data from being saved and display a validation text message to inform the user of the acceptable data format or criteria.
To use the Validation Rule property in a table field:

1. Open the Table in Design View:
   • Open your Access database, and in the Navigation Pane, right-click the table you want to add a validation rule to. Choose "Design View" from the context menu.
2. Select the Field:
   • Click on the field (column) for which you want to create the validation rule.
3. Set the Validation Rule:
   • In the field's property sheet (usually at the bottom of the window), find the "Validation Rule" property. Enter your rule using the appropriate syntax.
   
   For example:
   • To restrict a date field to future dates only, you might use `>Date()`.
   • For a number field to only accept positive values, use `>0`.
4. Set the Validation Text:
   • In the "Validation Text" property, enter the message that you want to display when the data does not meet the validation criteria. For example, for the positive number rule above, you could use "Please enter a positive number."
5. Save the Table:
   • Save your changes to the table.

To use the Validation Rule property in a form control:

1. Open the Form in Design View:
   • In the Navigation Pane, right-click the form you want to add a validation rule to and select "Design View".
2. Select the Control:
   • Click on the control (like a text box or combo box) you want to add a validation rule to.
3. Set the Validation Rule and Text:
   • In the control's property sheet, find the "Validation Rule" and "Validation Text" properties. Set them as described above, tailored to the data expected in this control.

Tips for Writing Validation Rules:

• Use Comparison Operators: Such as `=`, `>`, `<`, `>=`, `<=`, `<>` (not equal).
• Combine Conditions: Use `AND`, `OR`, and `NOT` to combine multiple conditions. For example, to restrict an "Age" field to values between 18 and 65, use `>=18 AND <=65`.
• Use Built-in Functions: For more complex rules, you can use Access functions. For example, `LIKE "M*"` to require text starting with "M".
• List Specific Options: Use `IN` to list acceptable values, like `IN ("Red", "Blue", "Green")` for a color field.

By carefully setting validation rules, you can ensure data integrity and guide users to enter data correctly, making your Access databases more robust and user-friendly.

Because data in database is the key word, it is essential that you make sure the data that comes into a database is correct. This is where the Validation Rule field-level property, otherwise known as a field property, comes in.
The Validation Rule field property is a feature that lets you place restrictions on data as it is being entered into a field in a table or a control on a form. The following series of images demonstrate how this feature works.

In Microsoft Access, the Validation Rule property is used to enforce specific constraints on the data that can be entered into a field in a table or a control in a form. The system will prevent the entry of data that fails to meet the conditions set in the rule. Here's a step-by-step process to set up a validation rule:

1. Open the relevant table in Design View. From the Navigation Pane, right-click the table you wish to apply validation to and select "Design View." This view allows you to alter the structure of your table.
2. Select the field for validation. Click on the field (column) in the table for which you want to implement validation. This action will open the "Field Properties" pane at the bottom of the screen.
3. Define the validation rule. In the "Field Properties" pane, click on the row labeled "Validation Rule". Here, you can type in the rule you want to enforce. For instance, if you wish to ensure a numerical field always receives a value greater than 4, your validation rule would be ">4".
4. Establish validation text. To help guide users, you can provide a message that will be displayed when the validation rule is violated. This is done in the "Validation Text" row in the "Field Properties" pane. A message corresponding to the previous example might be, "Please enter a value greater than 4."
5. Save your changes. After inputting your validation rule and validation text, save the changes to the table. Access will now enforce these validation rules anytime data is entered into this field.

Here are some examples of common validation rules:

1. To enforce an exact number of characters for a text field: If you want entries in a text field to be exactly five characters, your validation rule would be "LEN([FieldName])=5". The corresponding validation text might be, "Please enter exactly five characters."
2. To enforce date range constraints: If you want a date field to accept dates in the future only, your validation rule would be "[FieldName]>Date()". The corresponding validation text could be, "Please enter a future date."

Remember that validation rules in Access use a syntax akin to SQL, and they're not case sensitive. Use of logical operators like And, Or, and Not can combine conditions to form complex rules. Test your validation rules thoroughly to ensure they work as expected and maintain your database's data integrity.
Validation rules prevent bad data being saved in your table. Basically, they look like criteria in a query. You can create a rule for a field (lower pane of table design), or for the table (in the Properties box in table design.) Use the table's rule to compare fields. There is one trap to avoid. In some versions of Access, you will not be able to leave the field blank once you add the validation rule, i.e. you must enter something that satisfies the rule. If you need to be able to leave the field blank, add OR Is Null to your rule. Some versions accept Nulls anyway, but we recommend you make it explicit for clarity and consistency.

In the validation rule used for the table displayed in series of images above, the data entered into DateWorked field will be compared to the current date using Date() function.

The Date() function returns the current system date. A number of built-in functions can be used to make comparisons to dates and other types of data.
To check out some of the possibilities, use the Expression Builder by clicking on the Builder button

The Date function is a built-in Access function that returns the current system date, in other words, today's date. With this versatile function, you never have to hard-code today’s date in your calculations. That is, you can create dynamic calculations that use the current system date as a variable, giving you a different result every day. In this section, we look at some of the ways you can leverage the Date function to enhance your analysis.
Finding the number of days between today and a past date Imagine that you have to calculate aged receivables. You need to know the current date to determine how overdue the receivables are. Of course, you could type in the current date by hand, but that can be cumbersome and prone to error. To demonstrate how to use the Date function, create the query shown in Figure 2.20. Using the Date function in a criteria expression
You can use the Date function to fi lter out records by including it in a criteria expression. For example, the query shown in Figure 12.21 will return all records with an order date older than 90 days.

, next to the Validation Rule property. You can then work your way through the Expression Builder choices. You can see an example of this by working with the simulation in the lesson.

The syntax for the Validation Rule property looks like this:
<=Date()
where:
For operators, you use the standard =, <, >, <>, <=, and >= characters. The value to compare can be either a literal value or a returned value, as we haved used with Date(). Here are examples of Validation Rule property settings, along with their real-world descriptions.

Validation rules, sometimes referred to as "data integrity rules", form a critical part of the data management strategy in a database management system (DBMS). These rules essentially determine what kind of data is permissible for entry into the database and under what conditions. By applying validation rules at the table level, you ensure that data entering the database conforms to the specified conditions, thereby maintaining data quality and integrity. To implement validation rules, you must first understand the nature and characteristics of the data that should be stored within each table of your database. Each rule is custom-made, tailored to the specific requirements of each column in a table. There are several types of validation rules you can apply to a database, depending on your DBMS:

1. Data Type Validation: This is the most basic type of validation, ensuring that data entering a field aligns with its defined data type. For instance, you cannot enter text data into an integer field.
2. Range Validation: With this rule, you can specify a specific range of values allowed in a field. For example, a field storing age values might restrict input to the range 0-120.
3. List Validation: This rule restricts entries to a predefined list of acceptable values. This type of validation is commonly used for fields with categorical data.
4. Pattern Validation: This rule uses regular expressions to enforce a specific pattern to the data. It's typically used for fields like phone numbers or email addresses.
5. Referential Integrity: This is a crucial validation rule in a relational database, which ensures the relationships between tables are maintained. For example, a foreign key in one table must match a primary key in another table.

To set up validation rules at the table level, you will use SQL (Structured Query Language) constraints during the creation or alteration of your tables. Here is an example of how you might set a validation rule at the table level in SQL:

CREATE TABLE Employees (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int CHECK (Age>=18 AND Age<=65),
    Email varchar(255) CHECK (Email LIKE '_%@_%._%'),
    PRIMARY KEY (ID)
);

In this example, the CHECK constraint is used to set validation rules for the Age and Email fields. The age field must be between 18 and 65, and the email field must match the pattern of a typical email address. If data inserted into the table violates these rules, the DBMS will reject it.
It's crucial to remember that while validation rules are a powerful tool for data quality control, they are not foolproof. Therefore, they should be used in conjunction with other data quality management strategies, such as routine data audits and appropriate user access controls, to ensure comprehensive protection against bad data insertion.

Keep in mind that if you assign the Validation Rule property to a field at the table level and then assign a different Validation Rule property to a control at the form level, Access tries to use both properties to validate the data in that field.
An example of this would be if, for a field at the table level, you said that age had to be greater than 21, but for a field at the form level, you said that age had to be less than 21. You would not be able to enter any ages, because no entry could pass both validation rules.
The following example creates a validation rule for a field that allows only values over 65 to be entered. If a number less than 65 is entered, a message is displayed. The properties are set by using the SetFieldValidation function.

Dim strTblName As String, strFldName As String
Dim strValidRule As String
Dim strValidText As String, intX As Integer

strTblName = "Customers"
strFldName = "Age"
strValidRule = ">= 65"
strValidText = "Enter a number greater than or equal to 65."
intX = SetFieldValidation(strTblName, strFldName, _
    strValidRule, strValidText)

Function SetFieldValidation(strTblName As String, _
    strFldName As String, strValidRule As String, _
    strValidText As String) As Integer

    Dim dbs As Database, tdf As TableDef, fld As Field

    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(strTblName)
    Set fld = tdf.Fields(strFldName)
    fld.ValidationRule = strValidRule
    fld.ValidationText = strValidText
End Function

Be sure to take advantage of the Validation Rule property at the table-field level. It will aid you in your efforts to keep erroneous data out of your database.
In the next lesson, you will learn how to set the Validation Text property to display a custom message.

Click the Exercise link below to practice using the Validation Rule property.
Adding Validation Rule - Exercise