Home and Learn: Microsoft Access Course
Microsoft Access - Validation Rules
In previous lessons, we've covered field formatting in Access. Now, we'll take a look at data validation.
You can make sure users don't enter invalid data by a using a Validation Rule. In Design View, click inside the ShipDate item rather than the Phone item:
Notice in the area at the bottom, there is an item for Validation Rule:
However, we can't enter a validation rule here as we want to compare two fields in our table, ShipeDate and OrderDate. So we need to enter our rule in the Properties Sheet. If the Properties Sheet is not displayed on the right, click the Property Sheet item in the Show/Hide panel on the Table Design ribbon at the top of Access:
Alternatively, hold the ALT key on your keyboard and press the Enter key. This is what you should be looking at:
Click the little button with the three dots in it to the right of the Validation Rule item. The Expression Builder dialog box appears:
Notice that on the left-hand side, under Expression Elements, that the WidgetSalesData item is selected. In the middle, under Expression Categories, you have all the field names in the WidgetSalesData table. We want the ShipDate to be greater than the OrderDate. Double-click the ShipDate item and Access adds it to the Expression builder:
Access has put square brackets around the item to signal that this is a field in a table.
Now click on the Operators element in the left column:
In the middle, select the Comparison category:
From the Expression Values list, select the right pointy bracket (>):
Double click the right pointy bracket, which is the symbol for 'Greater Than'. Double-clicking will add the symbol to the Expression Builder at the top:
From the left, under Expression Elements, click back on the WidgetSalesData item. Under Expression Categories, double-click OrderDate. Your expression at the top will then look like this:
You could have just typed it all out yourself:
[Shipdate] > [OrderDate]
But it's nice to know that you don't have to!
Click OK on the Expression Builder dialog box. Notice that the validation rule is now filled out:
Now click into the area for Validation Text. Enter the following:
Ship Date can't be earlier than Order Date
Whatever you type for your validation text will appear in a message box, if the user gets it wrong. Let's see how it works.
Go back to Datasheet View. You'll be prompted to Save. After you save your work, you may see this message box appear:
This is saying that the new validation rule you set up hasn't been applied to any of the other records in your table, and do you want to check them for errors? Our order dates are all OK, so you can click No on this message box.
Close down the WidgetsSalesData tab and open up the Widgets table. Expand the Purple item at the bottom and enter a new value for ItemsSold. Click into the OrderDate box and select a date from the calendar. Now click into ShipDate box and enter a date that is earlier than the Order Date. Enter a phone number (enter it without a dash because Access will add that for you). Now click anywhere in the new row below. You should see your message box appear:
Go back and correct your error and you should be able to progress.
Access Comparison Operators
Here are some more comparison operators you can use:
| Operator | Meaning | Use |
| > | Greater Than | [Field1] > [Field2] |
| < | Less Than | [Field1] < [Field2] |
| >= | Greater Than or Equal To | [Field1] >= [Field2] |
| <= | Less Than or Equal To | [Field1] <= [Field2] |
| = | Equal To | [Field1] = [Field2] |
| <> | NotEqual To | [Field1] <>[Field2] |
You can also use these logical operators:
And
Or
Not
Eqv
Xor
Here are some examples:
AND
([ShipDate] > [OrderDate]) And ([Shipdate] > TODAY())
If both of these expressions are true, then Access returns a True value. If just one of them is false, then Access returns a value of False, and you'll get an error.
Or
([ShipDate] > [OrderDate]) Or ([Shipdate] > TODAY())
If just one of these expressions is true, then Access returns a True value. If just one of them is false, then Access returns a value of False, and you'll get an error.
Eqv
([ShipDate] > [OrderDate]) Eqv ([Shipdate] > TODAY())
If the expressions are both TRUE or both FALSE then Access returns true.
Not
([ShipDate] Not [OrderDate])
Returns TRUE when the expression is not true. In the example above, If the ShipDate is Not the same as the OrderDate then Access will return a value of True.
Xor
([ShipDate] > [OrderDate]) Xor ([Shipdate] > TODAY())
If just one of these is True then Access returns True. If both are True then Access returns False.
These Special Operators could come in useful:
Is Not Null
Like
Between
In
Is Not Null
[ShipDate] Is Not Null
Use this to check if a field value is Null.
Like
[phone] Like "075*"
You can check if a field value is like something else. For example, suppose you wanted to check if the phone number started with 075. You could do it like this:
The asterisk (*) means any number of characters. You can check for any single character with a question mark (?):
[phone] Like "075111?"
Between
[UnitsSold] Between 1 And 10
Check if a value is Between two other values. You can even check to see if dates come between two ranges:
[OrderDate] Between #20-02-2023# And #10-03-2023#
Each date goes between hash/pound symbols (#).
In
WidgetColor In ("Red", "Blue", "Yellow", "Pink", "Black", "White", "Green", "Brown", "Purple")
Check if a value entered is In a range of values:
Your list of items goes between round brackets. If your list is text items, each item goes between double quotes. You can use numbers, as well. Then you don't need the double quotes.
Strings
&
Combine one or more strings:
[Field1] & [Field2]
[Field1] & " " & [Field2]
+
Same as above. But, if one of your fields has a Null in it, Access returns Null for the whole item.
Exercise
Write a Validation Rule so that a user can't enter a value in the UnitsSold
column of the WidgetSalesData table that is 0 or less.
In the next lesson below, you'll start to query your data in more depth.
<-- Previous Lesson: Link Tables | Next Lesson: Asking Questions -->
<--Back to the Microsoft ACCESS Contents Page