5

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?

Improve this question

3 Answers 3

Reset to default
11

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Is the Jet/ACE version of IsNull() available here? I worry that the SQL Is Null is doing something different. I also wonder about using it on the Sum() instead of on the Amount field.
Yeah, without testing I'm not sure how Jet/ACE would handle summing with Nulls. It seems like, from a performance standpoint, if Jet/ACE treats Nulls as 0 for summing purposes, then it would be more efficient to check for Null once at the end instead of for each record.
I just ran a quick check and Sum() appears to treat Nulls as 0. I'd have to actually benchmark it to see if it made any difference performance-wise.
6

Pretty much the equivalent in Access is the nz function.

There's a good page on how to use it here.

However, if you're using Access just as a database backend and using Jet in your connectionstring then nz won't be available to you.

Improve this answer

4 Comments

Thanks hawbsl! But when i try to execute query using Nz from C# code, i get exception that Nz is not defined.
I found IsNull() is present within MS Access. But when using the query from c#, i m getting this error message - "Wrong number of arguments used with function in query expression 'IsNull(sum(amount),0)'"
Unfortunately if you're accessing the Access database through C# then you're using Jet rather than Access itself. Different rules apply and there are various useful functions which aren't available to you.
well, one way is to handle nulls in C# in your front end. sure that's no help if you're trying to use isnull inside a join but it doesn't look like you're trying to do that.
1

Likewise , can also be used on date and time when sorting

.....ORDER BY TRANSDATE ASC,(IIf([PaymentTime] Is Null, '23:59:59', [PaymentTime])) DESC
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.