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

Jet (the database engine behind Access) SQL also supports the Nz function. 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 2

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

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

Answer 3

Looks like I can just use:

SELECT ProductId, Nz(Price, 0)
FROM Products

Seems to be working just fine.