Examples of working with Null values using calculated fields

Examples of working with Null values using calculated fields

You enter the expression in the Field cell in the query design grid. The fields you reference in the expression are from the tables or queries in the current query.

Expression Description
CurrentCountry: IIf(IsNull([Country]), " ", [Country]) Uses the IIf and IsNull functions to display an empty string in the CurrentCountry field 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]) Uses the IIf and IsNull functions to display 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 first- and second-quarter sales fields combined, using the Nz function to convert the Null values to zero first.
IIf(IsNull([UnitPrice]),0,[UnitPrice]) Changes a Null value to a zero (0) in the UnitPrice field.

Note   When you use an arithmetic operator (+, -, *, /) in an expression and the value of one of the fields in the expression is Null, the result of the entire expression will be Null. If some records in one of the fields you used in the expression might have a Null value, you can convert the Null value to zero by using the Nz function, as shown in the preceding table. For more information on the Nz function, click .