CODEWITHSUNDEEP
sqlms-access
Nathan DeWitt
Nathan DeWitt

Reputation: 6601

coalesce alternative in Access SQL

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.

Upvotes: 39

Views: 81987

Answers (8)

user1945782
user1945782

Reputation:

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.

Upvotes: 0

Robert Gray
Robert Gray

Reputation: 1

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

Upvotes: 0

pipTheGeek
pipTheGeek

Reputation: 2713

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.

Upvotes: 25

iDevlop
iDevlop

Reputation: 25272

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

Upvotes: 9

user4406949
user4406949

Reputation: 9

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

Upvotes: -2

Art Mendenhall
Art Mendenhall

Reputation: 27

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.

Upvotes: 1

Nathan DeWitt
Nathan DeWitt

Reputation: 6601

Looks like I can just use:

SELECT ProductId, Nz(Price, 0)
FROM Products

Seems to be working just fine.

Upvotes: 9

Codewerks
Codewerks

Reputation: 5972

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

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

Upvotes: 26

Related Questions