Online Test Banks
Score higher
See Online Test Banks
eLearning
Learning anything is easy
Browse Online Courses
Mobile Apps
Learning on the go
Explore Mobile Apps
Dummies Store
Shop for books and more
Start Shopping

How to Keep Garbage Out of Your Access Database

Part of the 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.

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus

SERIES
Access 2010 All-In-One For Dummies Cheat Sheet

Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win $500. Easy.