4

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?

TylerH
  • 20,799
  • 66
  • 75
  • 101
IrfanRaza
  • 3,030
  • 17
  • 64
  • 89

3 Answers3

10

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.

mwolfe02
  • 23,787
  • 9
  • 91
  • 161
  • 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. – David-W-Fenton Jan 28 '11 at 23:38
  • 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. – mwolfe02 Jan 29 '11 at 03:09
  • 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. – mwolfe02 Jan 29 '11 at 03:11
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.

hawbsl
  • 15,313
  • 25
  • 73
  • 114
  • Thanks hawbsl! But when i try to execute query using Nz from C# code, i get exception that Nz is not defined. – IrfanRaza Jan 28 '11 at 12:13
  • 1
    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)'" – IrfanRaza Jan 28 '11 at 12:17
  • 1
    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. – hawbsl Jan 28 '11 at 12:18
  • 1
    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. – hawbsl Jan 28 '11 at 12:28
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
Kym NT
  • 670
  • 9
  • 28