Check null value in MS Access Query

Question

In SQL Server we can use IsNull() function to check whether expression value is null or not. For ex.

Select IsNull(sum(amount),0) as TotalAmount
  From Payments 

Likewise is there any function in MS Access Query to check the null? I need the same statement to be executed in MS Access Query.

Can anybody tell me the replacement for IsNull() in MS Access?

Answer 1

Using Jet/ACE your query can be re-written as:

SELECT IIf(Sum(amount) Is Null, 0, Sum(amount)) AS TotalAmount
FROM Payments

This should work even from C# because Is Null and IIf are both built in to Jet/ACE. Please note the space in Is Null and the lack of parentheses (it is a statement, not a function).

There are two added bonuses to using IIf and Is Null as opposed to Nz even if Nz is available to you:

• it executes faster because all the processing is done within the database engine (so it doesn't have to make function calls to the Access library)
• it retains the field's original type; because Nz returns a Variant, Jet/ACE is forced to display the result as a string (which is usually not what you want when dealing with dates, numerics, etc)

UPDATE: Allen Browne has an excellent primer on the use of IIf, Nz, IsNull(), and Is Null. I was planning on posting that link as my original answer, but I couldn't find the page at the time. I did the best I could from memory, but the true credit goes to Mr. Browne.