applied tips : january 2007

microsoft access : start the year off right

Unfortunately you can’t completely eliminate data entry errors in Access, but there is a lot you can do to reduce them. This article will show you how to increase accuracy and reduce data entry errors in your databases by specifying various field options.

Specify the data type

Since a field’s data determines the type of information that can be stored in a field, you can use it to ensure the correct type of information is being entered. For example, if a field stores dates, make sure its data type is set to Date/Time.

Set the field size

The Field Size property determines just how much information can be stored in a text or number field. For example, if you set the size of a text field to 2, you could enter “MN” but not “Minnesota.” Field Size works a little differently for text and number fields. In text fields, the Field Size determines the maximum number of characters that can be entered into the field. In number fields, the Field Size determines what type of number the field will accept. See the table below for available number field sizes and their descriptions.

Set the default value

If you specify a Default Value, that value is automatically entered in a field when a new record is created. For example, if most of your clients are from Texas, you could set the default value for the State field to “TX.” When a user adds a record to the table, they can either accept the TX default value for the State field or enter their own value.

Require data entry

You can specify that a field must contain data to prevent users from leaving out important information when they are entering data.

Use an input mask

An Input Mask limits the amount and type of information that can be entered in a field. You have probably already seen an example of an input mask on an ordinary paper form•the type of form that provides you with a guide like (___) ___ - _____ or ____-___-______ for entering phone numbers or social security numbers. There are two ways to create an input mask:

Use data validation

Without a doubt, data validation is the most powerful tool you can use to prevent data entry errors. With data validation, Access tests the data you enter to make sure that it conforms to what you want to appear in the table. If the incoming data doesn’t meet your requirements, Access refuses it and displays an error message.

Data validation works best in number, currency, and date/time fields. You can create a validation rule for text entries, but doing so can be complicated—especially if you want to test a lot of text variables.

There are actually two boxes that relate to data validation. They are the:

Creating data validation rules can be a little tricky—see the table below for some data validation examples.

schedule a class or consulting on access

Request an on-site Access class from Applied Office or get some helpful consulting. Sessions are just $95/hr.  Learn more here.

quick reference card

Get the Quick Reference Card on Microsoft Access! Download it for free and print it on your own printer. You might even want to laminate it.