Examples of expressions that check for null values

The following table lists examples of expressions that checks for null values when calculating a value.

You can use these expressions in calculated controls (calculated control: A control that is used on a form, report, or data access page to display the result of an expression. The result is recalculated each time there is a change in any of the values on which the expression is based.) on forms, reports, and data access pages (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.).

Expression Description
=IsNull([UnitPrice]) Returns True, if the UnitPrice field does not contain a value.
=IsNull("") Returns False, because a zero-length string is not a null value.
=IIf(IsNull([Region]),[City]&" "& [PostalCode],[City]&" "&[Region]&" "& [PostalCode]) Displays the values of the City and PostalCode fields if Region is Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain a Null value.); otherwise, it displays the values of the City, Region, and PostalCode fields, separated by spaces.

You can use these expressions in a calculated field in a query.

Expression Description
CurrentCountry: IIf(IsNull([Country]), " ", [Country]) Displays in the CurrentCountry field an empty string if the Country field is Null; otherwise, it displays the contents of the Country field.
LeadTime: IIf(IsNull([RequiredDate] - [ShippedDate]), "Check for a missing date", [RequiredDate] - [ShippedDate]) Displays in the LeadTime field the message "Check for a missing date" if the value of either the RequiredDate or ShippedDate fields is Null; otherwise, it displays the difference.
SixMonthSales: Nz([Qtr1Sales], 0) + Nz([Qtr2Sales], 0) Displays in the SixMonthSales field the total of the values in the Qtr1Sales and Qtr2Sales sales fields combined, first using the Nz function to convert any null values to zero.
 
 
Applies to:
Access 2003