Blocking Bad Data - Blank values and empty text
(Page 3 of 4 )
Access supports this Required property for every data type. However, with some data types you might want to add extra checks. That’s because the Required property prevents only blank fields—fields that don’t have any information in them at all. However, Access makes a slightly bizarre distinction between blank values and something called empty text.
A blank (null) value indicates that no information was supplied. Empty text indicates that a field value was supplied, but it just happens to be empty. Confused yet? The distinction exists because databases like Access need to recognize when information’s missing. A blank value could indicate an oversight—someone might just have forgotten to enter the value. On the other hand, empty text indicates a conscious decision to leave that information out.
Note: To try this out in your datasheet, create a text field that has Required set to Yes. Try inserting a new record, and leaving the record blank. (Access stops you cold.) Now, try adding a new record, but place a single space in the field. Here’s the strange part: Access automatically trims out the spaces, and by doing so, it converts your single space to empty text. However, you don’t receive an error message because empty text isn’t the same as a blank value.
The good news is that if you find this whole distinction confusing, then you can prevent both blank values and empty text. Just set Required to Yes to stop the blank values, and set Allow Zero Length to No to prevent empty text.
Note: A similar distinction exists for numeric data types. Even if you set Required to Yes, you can still supply a number of 0. If you want to prevent that action, then you’ll need to use the validation rules described later in this chapter (page 131).
Setting Default Values
So far, the fields in your tables are either filled in explicitly by the person who adds the record or left blank. But there’s another option—you can supply a default value. Now, if someone inserts a record and leaves the field blank, Access applies the default value instead.
You set a default value using the Default Value field property. For a numeric AddedCost field, you could set this to be the number 0. For a text Country field, you could use the text “U.S.A.” as a default value. (All text values must be wrapped in quotations marks when you use them for a default value.)
Access shows all your default values in the new-row slot at the bottom of the datasheet (Figure 4-4). It also automatically inserts default values into any hidden columns (page 91).
Access inserts the default value when you create a new record. (You’re then free to change that value.) You can also switch a field back to its default value using the Ctrl+Alt+Space shortcut while you’re editing it.
Figure 4-4.
This dating service uses four default values: a default height (5.9), a default city (New York), a default state (also New York), and a default country (U.S.A.). This
system makes sense, because most of their new entries have this information. On the other hand, there’s no
point in supplying a default value for the name fields
Tip: One nice feature is that you can use the default value as a starting point for a new record. For example, when you create a new record in the datasheet, you can edit the default value, rather than replacing it with a completely new value.
You can also create more intelligent dynamic default values. Access evaluates dynamic default values whenever you insert a new record, which means that the default value can vary based on other information. Dynamic default values use expressions (specialized database formulas) that can perform calculations or retrieve other details. One useful expression, Date( ), grabs the current date that’s set on your computer. If you use Date( ) as the default value for a date field (as shown in Figure 4-5), then Access automatically inserts the current date whenever you add a new record.
Note: You’ll learn much more about SQL expressions in Part Two.
Next: Preventing Duplicate Values with Indexes >>
More MS SQL Server Articles
More By O'Reilly Media
|
 This article is excerpted from chapter four of Access 2007: The Missing Manual, written by Matthew MacDonald (O'Reilly, 2006; ISBN: 0596527608). Check it out today at your favorite bookstore. Buy this book now.
|
|
