Ensuring the right data goes into Access

A Microsoft Access database is a great place to store and organize information since it gives you an unlimited number of ways to look at that data later. But what you can do with that data is limited by the quality of your data entry.

This can be a challenge, especially if you’re going to have a lot of different people working in your database. Using forms to limit and guide input is a good start, but doesn’t address the need for consistent data entry. If people are in a hurry and they aren’t looking where they’re typing, they can make all sorts of errors.

For example, if someone enters the date with the month before day, and another person puts it in the opposite way, you won’t be able to run any accurate queries based on date. And you wouldn’t be able to send out a mass mailing if someone accidentally typed phone numbers where the ZIP code was supposed to go.

Fortunately, Access gives you several tools to prevent those errors from making it into your database.

The most protection comes when setting up the fields for your database. You can specify the type of data that goes in each field. For example: text, dates, numbers, and currency.

You should always take advantage of these data types. While you can technically enter dates in a text field, if you’re only going to put dates in it, there’s no downside to making it a date field to begin with. Doing so prevents people from entering e-mail addresses, phone numbers, or other wrong information into it, and it makes it easier to run queries later.

For text fields, you can reduce some errors by giving the text field a fixed length. You should probably set limits for most text fields. There’s no reason a field for a customer’s first name, for example, needs 100 characters.

Input masks can also reduce errors. This option is available in the table properties and it shows your users how their entry should look:

For example, you could create an input mask in a date field that looks like 01/21/2010 to prompt your users to enter dates using the American date format. Office Online has a helpful article on creating input masks.

Finally, Access offers a feature called validation rules, which we'll delve into tomorrow. It sounds more complicated than it really is. Basically, it allows you to set rules for what people can enter into each field. If they type in something that doesn’t conform to those rules, they'll receive an error message – that you get to write! – and the user must try again.

Suzanne