coalesce alternative in Access SQL

Question

In T-SQL, you can do this:

SELECT ProductId, COALESCE(Price, 0)
FROM Products

How do you do the same thing in Access SQL? I see examples for doing it with Nz in VBA, but I'm looking for the SQL equivalent.

Thanks.

Peninsula

Answer 1

Access supports the Nz function and allows you to use it in a query. Note though that Nz is the same as the T-SQL ISNULL function. It can not take an arbitrary number of parameters like COALESCE can.

Beaston

"Jet (the database engine behind Access) SQL also supports the Nz function" - Incorrect, Jet has no NZ() function. The MS Access object model has a NZ() function which can be used in queries only within the MS Access interface. Using NZ() outside of the MS Access interface causes an error. – Keelson

For some reason NZ([Field], 0) changed the type of the field (left aligned in result set) while IIF ([Price] is null, 0, [Price]) was working well. – Alkmaar

FYI: The newer ACE database engine (replacement for older Jet engine) does support some VBA functions natively, including Nz(). I think that corresponds to Access 2010 and later. This is primarily necessary because the ACE engine supports Data Macros (similar to triggers in other SQL DBMS) which are supported directly, even outside the Access environment. The benefit is that such functions are also available in SQL queries. – Service

@CPerkins, I have ACE 2010 with pyODBC and I'm getting "[Microsoft][ODBC Microsoft Access Driver] Undefined function 'NZ' in expression". – Oersted

@CristianCiupitu Although ODBC will often pass SQL directly to the engine, I am aware that it sometimes does its own parsing and analysis. The ACE engine should support it, but the ODBC driver might be preventing it from being passed to the engine if its trying to validate the functions, etc. Can you share the full SQL statement or at least a snippet showing how it's being called? Is it properly formatted with correct parameters? Can you execute the exact same statement from within Access? – Service

@CPerkins, the query is something like SELECT NZ(SUM(price * quantity), 0) FROM outputs;. If I remove the NZ call it works fine. I don't have MS Access, just the driver. – Oersted

@CristianCiupitu I verified that Nz() is not allowed using various ODBC clients :(. But I also verified that Nz() is recognized by the ACE engine in other contexts. Consider that I get various error messages when using ODBC with calls to functions like DatePart() and Now(), but I still get valid data back. But if I try executing composite calls, ODBC complains and doesn't allow it even though it is perfectly good SQL format and the functions are allowed individually. In summary, nz() is indeed not supported but only because ODBC disallows it being passed to the engine by its own rules. – Service

Answer 2

If it's in an Access query, you can try this:

"Price = IIf([Price] Is Null,0,[Price])"

Sailmaker

Why not IIf(IsNull(Price), 0, Price)? – Ombre

How would you use this in the query? – Uveitis

@David-W-Fenton: because IsNull() is VBA and slows down thinks a lot, while Is Null is natively handled by Jet & co, See link to Allen Browne's page in my answer. – Carthusian

To clear up for future readers. IsNull is part of MS Access SQL. (not just VBA). You can use this function in backend ODBC/OLEDB queries unlike NZ. – Fan

Answer 3

Looks like I can just use:

SELECT ProductId, Nz(Price, 0)
FROM Products

Seems to be working just fine.

Peninsula

You do need to be careful about the resulting data type of the argument, as it doesn't always end up numeric when you expect it to. I've never quite figured out a pattern of why, though theoretically, it's supposed to pick up the data type of the first argument. – Ombre

Also, be careful about using VBA functions in queries, they can be performance killers. – Arboreous

Answer 4

Using Iif(Price is null, 0, Price) should give you the best performance (see Allen Browne's performance tips). However SQL Server Coalesce() has the great advantage over Iif() and Nz() that it can handle several parameters in a cascade. So I created this quick VBA equivalent:

Function Coalesce(ParamArray varValues()) As Variant
'returns the first non null value, similar to SQL Server Coalesce() function
'Patrick Honorez --- www.idevlop.com
    Dim i As Long
    Coalesce = Null
    For i = LBound(varValues) To UBound(varValues)
        If Not IsNull(varValues(i)) Then
            Coalesce = varValues(i)
            Exit Function
        End If
    Next
End Function

Carthusian

Answer 5

Using IsNull(), Nz(), and the data conversion functions are built-in VBA functions and will only slow down your queries in versions prior to 2003. As far as datatyping goes use CCur() to guarantee your data type, but only if you need to do strong comparisons or simply set the format property to Currency on the column. It is the IF statement that slows things the most, as it adds yet another function to your routine

using this solution: Nz([Price], CCur(0))

the only time CCur() will execute is when Price Is Null, so overall this is probably the fastest.

The point is that the least number of total functions used, the faster your queries will execute.

Caen

Answer 6

Unfortunately, my stackoverflow was using an old email account and I can't vote or reply individually to some of the responses because I don't have enough credibility.

Thank you for your posts. I added a public Function with a return type Double and used 0.00 as a default value. I called @iDevlop's Coalesce function such that the resulting value in the query is typed. The Nz() function would return the default value of 0.00 as 0 and I would have to multiply it by 1 to make it numeric and Access would then use its formatting.

Being used to sql-server, I missed the Coalesce function and this is really a time saver. I used Nz everywhere with IIf when it didn't work or if I had multiple values to choose in the query.

So thank you to the contributors on this page.

' Use Coalesce instead of Nz and return a double so that it displays correctly.

' If you pass back a 0, it will not be displayed a 0.00 unless you cast it as a fixed(2), or a double, or multiply a Nz returned result times a number.
Public Function CoalesceDbl(dbl As Variant) As Double
    CoalesceDbl = Coalesce(dbl, 0#)
End Function

Hydrocele

Answer 7

This is a very old thread, but I was interested to see if anyone had an alternative to COALESCE's multiple arguments. As for an answer to the above, you can use a horribly hacky version like this:

SELECT 
    CCur("0" & Price)
...

The reason this works is because the concatenator (&) foricbly converts NULL into an empty string when appending to another value.

This can be used to great effect when you're doing something like appending names and titles together that may contain NULL fields. For instance:

SELECT 
    [SURNAME] & ", " + [Forename] & " (" + [Title] + ")"

/*  └───────┘   └───────────────┘   └──────────────────┘
     Part 1      Part 2              Part 3  */
...

This example concatenates all results into a variant string. We then attempt to add (instead of concatenate) a ", " to the the [FORENAME] field. If this field is NULL then anything added to a NULL is NULL. We then attempt to concatenate the result of adding " (" and ")" to the [TITLE] field.

Parts 1, 2 and 3 are caluclated first, then concatenated together, resulting at worst in an empty string (not a NULL value). This works because concatenation has a lower priority than addition.

I used to use this and the conversion methods quite a bit, years ago. Please be warned that, as highlighted by @onedaywhen in the comments to the post by @pipthegeek, above, utilising VBA in your queries can make it sloooow.

Leonoreleonsis

Answer 8

COALESCE or NULLIF function are the standard used on sql server for a good migration to access. ISNULLor IIF or CHOOSE are nonstandard function.

Connett