Concerning the Access tip below* from today's MS Office Suite newsletter, an important clarification should be mentioned. As is the case for the Phone field in the Customers table of the Northwind sample database, one must ensure that the table property "Allow Zero Length" (strings) for the text field is set to "No".
When a new table is created in design view, the default setting for a text field is to allow zero length strings ("") as a value. Null basically means, "I don't know what the value is." Confusingly, null fields and zero length strings look the same to the "naked eye": just like an "empty" field. However, if some fields contain a zero length string and you query for "Is Null", those records will not appear in your query results. The query will still return records that do contain null values, and, in a large database, you might not realize that, say, customers without phone numbers were missing from the list.
Similarly, in a number field's table properties, one should replace the Default Value of "0" with "Null" to utilize this search strategy effectively. Otherwise, number fields skipped by the user will contain a value of zero by default, which is not the same as null ("I don't know the value.") and will not return a result for "Is Null".
Hope this is helpful,
Russ
*Search for fields with no data
>It's not uncommon for a field to have no data. >A user may have skipped the field or left it >blank because there was no value for the field >at the record's time of entry. For example, >phone numbers may be missing for some customers >in your Customer table.
>To find all customers whose phone number field >contains no data, follow these steps:
>Create a query using the Customer table.
>Select the Name and Phone Number fields from >the Customer table for the query.
>Enter Null in the Criteria cell of the Phone >Number field. (Access automatically replaces >the entry with Is Null.)
>When you run the query, the results will list >all customers whose phone numbers are missing.
>But suppose your salespeople need a list of all >customers they can call for a special >promotion. To create a list of all customers >with phone numbers, follow the same steps.
>But this time, enter Not Null in the Criteria >cell. Access automatically replaces the entry >with Is Not Null, and the query results will >list all customers whose phone number fields >are not blank.