These Access pointers will help you understand and effectively address null values in various situations. This article is also available as a PDF download. Where errors are concerned, null values are an equal-opportunity menace. If an unhandled null value doesn't generate a runtime error, it'll show up in erroneous data.
Neither problem is your run of the mill "oops, there's a bug" error. In fact, an unhandled null value is the sign of a lazy or inexperienced developer. When null values are acceptable values, and they often are, you must handle them upfront and aggressively. You can't handle a value properly if you don't understand its nature. A common misconception is that a null value is simply an empty field or no value at all. That's not true. A null value indicates that the data is missing or unknown.
Occasionally, a null value does mean that the data doesn't exist or isn't valid for that particular record, but the concepts aren't interchangeable. Since Access allows null values, it's your job to determine whether you want to store them.
Generally, the data will be your best guide. If the nature of the data requires that all data be present to save the record, you can handle null values at the table level. Simply set the field's Required property to Yes and bypass the problem. Be prepared for the rules to change. Few applications are so tight that nulls aren't present.
If users need the flexibility to create records without entering all of the data at the time they create the record, you have a choice. Allow the table to store a null value or use a default expression that stores an appropriate text message, such as "NA" or "Pending.
Unfortunately, this solution works only for text fields. For numeric fields, you could use a default value of 0, but that might cause trouble in the long run because functions handle Null and 0 differently see 7.
In addition, the Default property works only for new records. That means that you can't apply this solution to existing records. The truth is, it's usually easier to handle null values than it is to usurp them in this fashion.
Don't try to find null values by equating them to anything else. The following expressions return an error, regardless of anything 's value:. As far as Access is concerned, Null doesn't equal anything.
This isn't always true outside Access. Once you decide that null values are acceptable, it's your job to accommodate them throughout the application. For instance, to find null values in a query, you'd enter Is Null in the appropriate field's Criteria cell. Consider this innocent-seeming example:. This expression runs into trouble if Quantity is null. Nulls have a strange way of spreading, somewhat like an invasive fungus. If you have a null anywhere in a calculation, the result of that calculation is automatically null.
In this example, that means the OrderItemCost for that record becomes null. Even worse, if the OrderItemCost enters into another calculation or a subtotal, that too becomes null. Before you know it, your valuable query data turns into a ream of empty cells.
To correct this problem, use the Nz function to clean up any potential nulls in optional fields:. Finally, you can use Nz to supply a different value altogether.
In a text field, you may choose to enter something more descriptive. Note: The characters? The following examples are for the UnitPrice field in a query that is based on a table that stores products information.
The criterion is specified in the Criteria row of the field in the query design grid. Use this crit erion. The following examples are for the OrderDate field in a query based on a table that stores Orders information. Returns records of transactions that took place on Feb 2, Remember to surround date values with the character so that Access can distinguish between date values and text strings. You can also use the Between operator to filter for a range of values, including the end points.
Returns records where the transactions took place on Feb 1, , March 1, , or April 1, Contain a date that falls in a specific quarter irrespective of year , such as the first quarter. Returns records of transactions that took place on the current day.
Returns records of transactions that took place the day before the current day. Returns records of transactions that took place the day after the current day. Returns records of transactions that took place during the current week.
A week starts on Sunday and ends on Saturday. Returns records of transactions that took place during the last week. Returns records of transactions that will take place next week. Returns records of transactions that took place during the last 7 days. Returns records for the current month.
Returns records for the previous month. A month's worth of sales records. Returns records for the current quarter. Returns records for the previous quarter. Returns records for the next quarter. Returns records for the current year. Returns records of transactions that took place during the previous year.
Returns records of transactions with next year's date. Returns records of transactions with dates that fall between Jan 1 of the current year and today. Tested for a Yes value.
A value of 1 or -1 is converted to "True" in the Criteria row after you enter it. Tested for a No value. A value of 0 is converted to "False" in the Criteria row after you enter it. Attachments In the Criteria row, type Is Null to include records that do not contain any attachments. Type Is Not Null to include records that contain attachments. Lookup fields There are two types of Lookup fields: those that look up values in an existing data source by using a foreign key , and those that are based on a list of values specified when the Lookup field is created.
Lookup fields that are based on a list of specified values are of the Text data type, and valid criteria are the same as for other text fields. The criteria you can use in a Lookup field based on values from an existing datasource depend on the data type of the foreign key, rather than the data type of the data being looked up. For example, you may have a Lookup field that displays Employee Name, but uses a foreign key that is of the Number data type.
If you do not know the data type of the foreign key, you can inspect the source table in Design view to determine the data types of the field. To do this:. The data type for each field is listed in the Data Type column of the table design grid. Multivalued fields Data in a multivalued field are stored as rows in a hidden table that Access creates and populates to represent the field. In query Design view, this is represented in the Field List by using an expandable field.
To use criteria for a multivalued field, you supply criteria for a single row of the hidden table. Just below the name of the field, you will see a field representing a single value of the multivalued field. This field will have the same name as the multivalued field, with the string.
0コメント