IsNull Function

Applies to: Access for Microsoft 365, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007.

Returns a Boolean value that represents whether an expression includes no valid data (Null).

Syntax

IsNull ( expression )

The necessary expressionargument is a Variant including a numeric expression or string expression.

Remarks

IsNull returns True if expression is Null; otherwise, IsNull returns False. If expression comprises several variables, Null in any constituent variable results in True to be returned for the whole expression.

The Null value represents that the Variant contains no valid data. Null is different from Empty, which denotes that a variable is still awaiting initialisation. It is even an irregular length compared to a zero-length string (“”), which is often called a null string.

Important:  Utilise the IsNull function to determine whether an expression contains a Null value. Expressions that you may expect to evaluate to True under some circumstances, such as If Var = Null and If Var <> Null, are constantly False. This is because any expression having a Null is itself Null and, therefore, False.

Query examples

ExpressionResults
SELECT ProductID, isNull(Discount) AS Expr1 FROM ProductSales;Returns values from the field “ProductID”, evaluates if the values in the field “Discount” are Null, returns -1 (True) and 0 (False) in column Expr1.
SELECT ProductID, isNull(Discount) AS NullStatus FROM ProductSales;Returns values from the field “ProductID”, evaluates if the values in the field “Discount” are Null, returns -1 (True) and 0 (False) in column NullStatus.

VBA example

Note: Examples that follow demonstrate the use of this function in a Visual Basic for Applications (VBA) module. For more information about working with VBA, select Developer Reference in the drop-down list next to Search and type one or more terms in the search box.

This example uses the IsNull function to decide if a variable contains a Null.

Dim MyVar, MyCheck
MyCheck = IsNull(MyVar) ' Returns False.
MyVar = ""
MyCheck = IsNull(MyVar) ' Returns False.
MyVar = Null
MyCheck = IsNull(MyVar) ' Returns True.

This information was compiled using information courtesy of © Microsoft 2020. All rights reserved.

Leave a Reply

%d bloggers like this: