How to Keep Garbage Out of Your Access Database - dummies

How to Keep Garbage Out of Your Access Database

By Alison Barrows, Margaret Levine Young, Joseph C. Stockman

Part of Access 2010 All-In-One For Dummies Cheat Sheet

If the data that goes into your database through tables and forms is garbage, then 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 to 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: Limits the number of characters. For instance, if you know that a field should never exceed four characters, set the field size to 4 characters.

  • Format: Makes the data look right. For instance, you can change text to all caps or all lowercase. Input masks, for example, work with the Format field property.

  • 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 — for instance, if an order number has two letters followed by four digits. Phone numbers and ZIP codes are other examples of fields where input masks are useful.

  • Default Value: 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: A rule that data must pass before it is entered. This property works with the Validation Text property rule.

  • Required: Specifies that the field must have a value in order 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: Specifies whether a zero-length entry such as “” (quotes without a space between them) is allowed (only for Text, Memo, 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 is a check box on the Datasheet tab of the Ribbon.

  • Lookup Fields: Allow the user to select a value for a field that is stored in another field. This eliminates many misspellings and standardizes the options for the field. Use the Lookup Wizard to create a Lookup field.