Ms Office Tune Up

Access may be a powerful program, but it’s not stretch to say that it’s only as useful as its data i accurate. You can create the world’s most powerfu queries, the most efficient forms, and the best-lookin reports, but none of it means a thing if the dat in the underlying table is either downright wron (for example, if digits get transposed) or invalid (fo example, a field value falls outside an allowabl range of values). Slips of the fingertips always happe during data entry, but Access does offer a fe tools that you can use to lessen the chance that yo or someone else enters invalid data

One simple way to help users enter valid data is to include a description for each field in table. As you can see in Figure 1, in the table Design view (right-click the table in th Navigation pane and then click Design View), you can use the Description column to ente brief descriptions of each field. These descriptions can also include data-entry instructions such as the following for the Order Date field

The order date in MM/DD/YYYY format

In Datasheet view (choose Design, click the bottom half of the View split button, and the click Datasheet View), the Description text displays in the Access status bar when the use enters the field, as shown in Figure 2.

Figure 1. You can add Description text for each field in you table Status bar field descriptions can help users enter data more accurately but, of course, the are useful only if the user actually reads them. Unfortunately, people all too often mis such prompts while in the throes of data entry

Access helps ensure valid data by alerting the user when he enters data that doesn’t confor to the field’s data type. Access automatically displays a smart tag with the followin message (where Type is the field’s data type; see Figure 3 for an example)

The value you entered does not match the Type data type in this column

Figure 2. The current field’ Description text display in the status bar

Figure 3. If you try to enter a valu that doesn’t match th field’s data type, Acces displays a smart tag simila to the one show here

Ensuring data type consistency is just one of the battles that database designers must wage The other major battle is ensuring that the entered data is accurate and valid. If you wan to enhance the accuracy and validity of entered data, you need to automate data checkin to a certain extent by implementing validation rules. These are conditions that specify th legal values that the user can enter For example, in a table of orders placed, suppose you want to avoid order dates that are i the future. In your business, orders are placed today or perhaps in the recent past, bu never in the future. To ensure this, you can set up a validation rule that allows the data onl if the order date is less than or equal to today’s date. For invalid entries, you can define validatio text that pops up to alert the user that he has entered an invalid value

Here are the steps to follow to set up validation rule

1. Open the table you want to work with in Design view (right-click the table in th Navigation pane and then click Design View)

2. Click the field to which you want to apply the validation rule

3. Click inside the Validation Rule property

4. Type a formula that specifies the validation criteria. You can either enter the formul directly into the property box, or you can click the ellipsis (…) button and create th formula using the Expression Builder

5. Click inside the Validation Text property and then specify a message that you wan Access to display if the user enters an invalid value

If your table already includes data in the field to which you applied the validation rule, it’s a goo idea to run a test on the existing data to ensure that it satisfies the new rule. In the table Desig view, choose Design,Text Validation Rules. Note, too, that Access also prompts you to test your validatio rules when you save the table after modifying the Validation Rule property

Figure 4 shows an example validation rule and text for the Orders table’s Order Dat field. We want this value to be equal to or before today’s date, which is given by the Date function

<=Date(

Figure 4. Use the Validation Rule property to enter a dat validation expression fo a field

In Figure 4, you see that there is also a string in the Validation Text property. If th users enter invalid data (that is, any value for which the Validation Rule expression return False), the validation text displays in a dialog box, as shown in Figure 5

Figure 5. If the users enter invali data in the field, Acces displays a dialog box such as this one, whic huses the string entere into the Validation Tex property

As a slightly more complex example, suppose you have a field for a discount rate an you’ve set up that field with the Percent number format. (In the table’s Design view, selec Percent in the Format property.) This speeds data entry because to enter, say, 25 percent the user needs to enter only the 25, and Access adds the percent sign (%) automatically

Unfortunately, some users might enter 0.25, which means the data goes into the table a 0.0025, which is invalid. In other words, you want users to enter values that are less than o equal to 100, but also either 0 or greater than 1. Here’s an expression that you can use i the Validation Rule field to ensure the correct data range for the Discount field

<=1 And (=0 Or >=0.01

This expression says that the data value must be less than or equal to 1 and either 0 o greater than 0.01. (Remember that the validation rule is applied to the data that is actuall stored in the field. So even though, in this case, the user enters, say, 25, the data is actuall stored in the field as 0.25.)

Popularity: 3% [?]