Show All

Distinguish between Null values and zero-length strings (MDB)

Note  The information in this topic applies only to a Microsoft Access database (.mdb).

When you view data in a field that contains both Null values and zero-length strings, the fields look the same— they contain no values. However, you can use an expression to distinguish Null values from zero-length strings.

1. Open a query, form, report, or data access page in Design view.

2. In a field in the query design grid, or in the control source of an unbound control, type the following expression. Substitute for fieldname the name of the field that contains both Null values and zero-length strings.

   =IIf(IsNull([fieldname]),"Unknown",Format([fieldname],"@;\ZLS"))

   The expression returns "Unknown" if the field contains Null and "ZLS" if the field contains a zero-length string; otherwise, the expression returns the value in that field.