MS Access IsNull Function with Examples in SQL Query and VBA Code

Null is empty field that indicate the data is missing or unknown. IsNull function is the opposite of Is Not Null function. Normally, the attributes in field database are set to allow Null value except for Primary Key field.

The syntax for IsNull function is:

( expression ) IsNull

Parameter:

Expression: the required SQL statement for returning the result. The IsNull function is only valid for SQL.

Unlike Is Not Null, IsNull returns True if expression is Null; otherwise, IsNull returns False.

Example of IsNull function in Query:

The table above named Borrow Table in Library Database. You can see that the ReturnDate field has the null value, meaning that the book is still borrowed and has not returned yet. Meanwhile, we need to create a field named Status to display whether the book is returned or not.

1. Create a new query by going to Create tab > Queries group > Query Design.
2. Show the table that wanted to be displayed.
3. In the Field row, input the syntax:

Status: IIf ([ReturnDate] Is Null, “Borrowed”, “Returned”)

Note: yup, it’s Is Null in query instead of IsNull.

4. In Design tab > Result group, click Run.
5. Switch to Datasheet View to test the query and see the changes.

Example in SQL (Query):

We still using the table above for example. Now, the case is to show the book borrowing records where the members still has not returned the borrowed books yet.

1. Switch the Query view into SQL view.
2. Input this syntax to the query:

Select * from Borrow

Where ReturnDate Is Null;

3. Hit Design tab > Result group > Run.
4. Switch back to Datasheet view to see the result:

Example in VBA Code:

Dim test As Boolean

test = IsNull(“”)

Return: True

In the example in VBA, it is the simple True or False. Since the ABC has no value or has not filled, it is counted as True. If test has any value, it will returned as False:

test = IsNull(“A Be Ce De”)

Return: False