If the data that goes into your database through tables and forms is garbage, any output or analysis you do with queries and reports will give you garbage too. Fortunately, Access offers lots of tools to help you make sure that the data that goes in each field is the data that’s supposed to go in that field. You can find many of the tools that keep garbage out in Table Design view:
Data Type: Use the correct data type to prevent data of the wrong type from being entered.
Field Size: This property limits the number of characters. If you know that a field should never exceed four characters, for example, set Field Size to 4.
Format: This property makes the data look right. You can change text to all caps or all lowercase, for example.
Input Mask: An input mask limits the information allowed in a field by specifying what characters you can enter. Use an input mask when you know the form the data should take — if an order number has two letters followed by four digits, for example. Phone numbers and zip codes are other examples of fields in which input masks are useful. Input masks work with the Format field property.
Default Value: This property defines a value that appears by default if no other value is entered. The default value appears in the field until another value is entered.
Field or Record Validation Rule: Data must pass this rule before it’s entered. This property works with the Validation Text property rule.
Required: This property specifies that the field must have a value for you to save the record. When no value is entered, Access doesn’t create a new record when Tab or Enter is pressed, and the New Record button is grayed out.
Allow Zero Length: This property specifies whether a zero-length entry such as “” (quotes without a space between them) is allowed (only for Text, Long Text, and Hyperlink fields). A zero-length field allows you to differentiate between information that doesn’t exist and a null value (blank) that is unknown or hasn’t been entered. When this option is set, it allows a zero-length string in a required field. You may want to use an input mask to make a zero-length field look different from a null value when both are allowed.
Indexed: When you choose to index a field, you can specify that no duplicate values are allowed in the field. This property is also accessible from Datasheet view; it’s a check box on the Datasheet tab of the Ribbon.
Lookup Fields: This property allows the user to select a value for a field that’s stored in another field, thereby eliminating many misspellings and standardizing the options for the field. Use the Lookup Wizard to create a lookup field.
