Access 2010

Field character limits

Setting the character limit for a field sets a rule about how many characters—letters, numbers, punctuation, and even spaces—can be entered in that field. This can be useful to keep the data in your records concise, or even to force a user to enter data a certain way.

For instance, in the example below, the user is entering records that include addresses. If you set the character limit in the "state" field as "2," the user can only enter 2 characters of information. This means he must enter postal abbreviations for the states instead of their full names—here, NC instead of North Carolina. Note that you can only set a character limit for fields defined as text.

To set a character limit for a field:

1. Select the desired field.
2. Click the Fields tab in the Table Tools tab group, then locate the Properties group.
3. In the box next to Field Size, type the maximum number of characters you would like to allow in your field.
   Setting a 2-character limit on the State field
4. Save your table.

Validation rules

A validation rule is a rule that dictates what information can be entered into a field. When a validation rule is in place, it is impossible for a user to enter data that violates that rule. For example, if we were asking the user to input a state name into a table with contact information, we might create a rule that limits the valid responses to U.S. state postal codes. This would prevent users from typing something that wasn't actually a real state postal code.

In the example below, we will apply that rule to our Customers table. It's a fairly simple validation rule—we'll just name all of the valid responses a user could enter, which will mean the user can't type anything else into the record. However, it's possible to create validation rules that are much more complex. For detailed information on how to write validation rules, review this official Microsoft office tutorial on creating validation rules.

To create a validation rule:

1. Select the field where you wish to add a validation rule. For our example, we'll set a rule for the State field.
2. On the Ribbon, select the Fields tab, then locate the Field Validation group. Click the Validation drop-down command, then select Field Validation Rule.
   The Field Validation Rule command
3. The Expression Builder dialog box will appear. Click the text box, and type in your validation rule.
   
   In our example, we want to limit data in the State field to actual state postal codes. We'll type each of the valid responses in quotation marks and separate them with the word Or, which lets Access know that this field can accept the response "AL" or "AK" or "AZ" or any of the other terms we've entered.
   Typing our validation rule, which will contain every state postal code, each separated by the word "Or"
4. Once you're satisfied with the validation rule, click OK. The dialog box will close.
5. Click the Validation drop-down command again. This time, select Field Validation Message. 
   The Field Validation Message command
6. The Enter Validation Message dialog box will appear. Type the phrase you would like to appear in an error message when a user tries to enter data that violates the validation rule. Your message should let the user know what data is permitted.
   Typing the phrase that will appear as an error message when someone attempts to break the validation rule
7. When you're satisfied with the error message, click OK.
8. The validation rule is now included in the field. Users will be unable to enter data that violates the rule.
   Data has been entered that violates the validation rule

Simple validation rules can be written exactly like query criteria. The only difference is that query criteria search for data, while an identical validation rule either permits or rejects data. To see examples of query criteria, review our Query Criteria Quick Reference Guide.