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:

Ms Access Isnull-1

Ms Access Isnull-1

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.

  1. In Design tab > Result group, click Run.
  2. Switch to Datasheet View to test the query and see the changes.
Ms Access Isnull-2

Ms Access Isnull-2

Example in SQL (Query):

Read Also:  How to Create or Solve MS Access Enter Parameter Value

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;

  1. Hit Design tab > Result group > Run.
  2. Switch back to Datasheet view to see the result:
Ms Access Isnull-3

Ms Access Isnull-3

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

Related Access Database

  • ms access code isnull


Access Database Tags: #access if null then 0 #if isnull access #is not null access query #ms access coalesce #ms access if null #ms access is not null #ms access isnull #ms access isnull equivalent #ms access isnull expression #ms access isnull function #ms access isnull query #ms access isnull replace #ms access nvl #what does isnull mean #what is isnull #what is isnull in access

Ms Access Subquery in Select Statement Syntax
Ms Access Subquery in Select Statement Syntax
Ms Access Subquery syntax is the Select
MS Access Tab Control in Form Tables or Subform
MS Access Tab Control in Form Tables or Subform
Access Tab Control is mainly used in
MS Access Left Function in SQL Query and VBA Code
MS Access Left Function in SQL Query and VBA Code
Ms Access Left Join function or sometimes
MS Access Inner Join on Multiple Fields and Conditions
MS Access Inner Join on Multiple Fields and Conditions
Ms Access inner join function is another


Must read×

Top