Access 2010 Field Validation Rule & Message

In Access, Field Validation lets user to validate the data entry by applying desired constraints over the field, it also facilitates users to show the messages in case of any invalid data entry field. By writing simple expression in validation fields you can collate entries from different fields and apply rules over them. Expressions are used in many places to perform calculations, manipulate characters, or test data. You can apply different expression over tables, queries, forms, reports, and macros for validation. This post will describe the most easiest way to apply rules and show messages in case of any invalid data entry.

Launch Access 2010, open a table on which you want to apply validation rule.

table access

Select the field on which you want to apply field validation. For instance, we want to apply field validation column Marks.

marks

Now navigate to Fields tab, from Validation click Field Validation Rule, to write an validation expression

field validation

You will reach Expression Builder dialog, here you can write expression to validate the field entries. In our case all courses carries 100 marks, so marks obtained must be less than 100,

Syntax

[Field_Name]<Numeric_Value

We will write a simple expression to validate Marks field as shown in the screenshot below. Under Expression Element, Categories, and Values, there are loads of expressions which can be applied over the selected field.


We will write the formula,

[Marks]<100

marks expressin

Click OK, to apply it over.

Now for users, we need to write a message which helps them to understand the condition we have applied, head over to Fields tab, and from Validation click Field Validation Message.

message 1

Enter message you want to show, in case of any invalid entry. Click OK to continue.

message dialog

If invalid entry is entered, the validation message will pop-up, preventing users to enter it.

invalid entry

You can also check out our previously reviewed guides on Creating Crosstab query in Access 2010 & Importing Worksheet in Access 2010.

Advertisement