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.

Answer 1

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.

Answer 2

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

Answer 3

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 Integer
    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

Answer 4

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

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

Answer 5

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 arbitary number of parameters like COALESCE can.

Answer 6

Looks like I can just use:

SELECT ProductId, Nz(Price, 0)
FROM Products

Seems to be working just fine.