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:
- Click the Build button (click inside the Input Mask field to display this button) to have the Input Mask Wizard create the input mask for you (the fast and easy way). The only problem with the Input Mask Wizard is that it can only help you create input masks for phone numbers, Social Security numbers, Zip codes, and date and time fields.
- Create the input mask yourself by typing a series of characters in the Input Mask box (the hard way).
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:
- Validation Rule box: Use this box to specify the requirements for data entered into the field.
- Validation Text box: Use this box to specify the message that you want to display to the user when data that violates the validation rule is entered.
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.