Examples of expressions that check for null values - Access

Warning: You are viewing this page with an unsupported Web browser. This Web site works best with Microsoft Internet Explorer 6.0 or later, Firefox 1.5, or Netscape Navigator 8.0 or later. Learn more about supported browsers.

Email this link

Printer-Friendly Version Bookmark and Share

Examples of expressions that check for null values

Applies to: Microsoft Office Access 2003

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 Null.); 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.

Related Office Online discussions

Read related questions and answers from other Microsoft Office customers.