Access text validation rule examples

On the previous page you saw how you create a simple validation rule in Access. On this page we will have a closer look at text validation rules in Access. This page contains validation rule examples for an email address, a username and a zip code.

Validating text

Validation rules for text allow you to check if text has a certain length or contains certain characters or sequences of characters. Access provides an expression language that allows you to express such rules. This expression language contains elements of regular expressions. Regular expressions, or regex in short, is a language that is used in computer programming to match text against certain criteria.

Expressions like the ones you will see on this page are not used just in validation rules. They are used in other parts of Access as well, most importantly in SQL queries to match records against certain criteria. But let's not get ahead of ourselves and look at some text validation rule examples.

The LIKE keyword

In Access text validation rules you use the LIKE keyword in combination with a pattern to indicate that the value entered for a field must match that pattern.

If you type the following validation rule into the Validation Rule field of your table field, that field will only accept values that start with the letter J, or, since Access is case-insensitive, with the letter j.

Like "J*"

The following pattern will match only the words "Cuckoo Bananas". Using this pattern wouldn't make much sense in a real database, because if the value of a field must always be "Cuckoo Bananas", why would you store that data in the first place?

Like "Cuckoo Bananas"

Validation rule patterns usually match a certain range of possible input values. This range is expressed in the pattern by including variable parts that are called wildcards. The asterix symbol * is one such wildcard symbol.

Wildcard symbols

Below are the wildcards you can use in Access input validation rules.

Symbol Explanation
* The asterisk symbol matches any number of characters, including zero/no characters.
? The question mark symbol matches a single character. It doesn't match no character.
# The number sign matches numbers (0-9)
[characters] Matches the characters inside the square brackets
[!characters] Matches the characters that are not between the brackets
[a-f] Matches the range of characters between a and f.

The symbols in this table are used to create patterns that new values are tested against. Let's look at some simple examples.

Email address validation example

The following validation rule example is a simple email address validation.

Like "*@*.???"

This rule will match any text that contains an @ and a . in that order. So it will also validate the input @. or 3@.. These can hardly be called email addresses. Let's make the validation rule a bit smarter by adding some minimum length restrictions with the ? symbol.

Like "?*@?*.??*"

Our email validation rule now requires at least on character on the left side of the @, at least one character between the @ and the . and at least two characters after the *. This is starting to look more like email address validation.

Improving our email validation rule: the Not keyword and the ! symbol

Our email validation rule could still be improved, because the email address êkej∩◘ would still be validated.

The ê and ◘ are all special characters that are not allowed in the local part (the part before the @) of email addresses. According to the official specification for email addresses, the local part of an email address can contain characters in the range a-z (or A-Z), 0-9 and the following special characters: @=.^_$%!#&'`{|}*?~/-

Now, in order to apply these restriction we should test only the local part of the email address, but we would need functions to accomplish this and I will get to functions later.

We could improve our email validation rule example now by testing the entire email address for characters that are not in the a-z, 0-9 and @=.^_$%!#&'`{|}*?~/- range. 

Like "?*@?*.??*" And Not Like "*[!a-z@=.^_$%!#&'`{|}*?~/-]*"

This validation rule says: I will accept anything that has at least on character, then an @, then at least on character, then a ., then at least two characters And none of these characters are NOT (!) within the a-z range or in the  @=.^_$%!#&'`{|}*?~/- collection.

Wow. That's quite a rule. And I introduced three two new operators. First, the And operator is used to apply multiple rules at the same time. Secondly, the Not operator let's Access test if the supplied value does Not match the pattern. And thirdly, the exclamation mark at the beginning of the character class negates the character class. It says "is not in this character class".

All operators combined, the second part of the expression says "and allow characters that are NOT NOT in this collection". In other words, what this really says is "allow only characters that ARE in this collection", because NOT being NOT in a collection is the same as BEING in a collection.

With our new validation rule the email address êkej∩◘ is not allowed anymore, so our rule has improved. It is good, but not yet perfect.

This input validation example still lacks some features.

  • It doesn't test for a maximum length of the email address or its parts.
  • It allows special characters (@=.^_$%!#&'`{|}*?~/-) in the domain and domain extension part of the email address.

In order to tackle these shortcomings, we will have to take a look at functions, and more specifically the InStr and Mid function that are needed to split the input value in parts that we can validate individually. We're getting to functions later. First, let me show you an example of how a ZIP code could be validated.

ZIP code validation rule example

An American ZIP code contains five numbers and optionally a hyphen and four more numbers.

Normal ZIP code: 12345 
Extended ZIP code: 12345-6789

An Access validation rule that validates these codes could be:

Like "#####" Or Like "#####-####"

The number symbol # requires a number, so this validation rule example says it will validate either a five position number like 29839 Or a five position number followed by a hyphen and then foillowed by a four position number, like 39872-3982.

Logical operators

The Or keyword is a logical operator, just like And. And is used if both of the expressions must be valid and Or is used when either one of the expressions must be valid.

Username validation rule example

Usernames in software system usually conform to the following rules.

  • They don't contain spaces.
  • They contain only characters between a-z and 0-9 and they don't contain special characters.
  • They have a minimum and maximum length.

We are going to drop the minimum and maximum length constraints right away, because we need functions to accomplish this. We can't incorporate a maximum length check in our validation rule directly, but we could still restrict the maximum length by setting  a length on the username field.

Here is a valdation rule example for usernames.

Not Like "*[!a-z0-9]*"

Just like in the email address validation example we are using what is called "double negation" to validate the input. Double negation is repeating a negation twice, like saying "I am not not 16 years old", which means you are sixteen years old. In the username validation example we are saying "I validate characters that are Not Not in the a-z0-9 collection". The first not is part of the Not Like expression and the second not is represented by the exclamation mark ! that starts the character class. So this expression only validates characters from the a-z and 0-9 ranges.