Coalesce Alternative in Access SQL

coalesce alternative in Access SQL

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.

COALESCE, IFNULL, or NZ() function that can be used in SQL Server and MS Access

This will work, but it's clunky:

SELECT Amount 
FROM PaymentsDue
WHERE Amount IS NOT NULL
UNION ALL
SELECT 0 AS Amount 
FROM PaymentsDue
WHERE Amount IS NULL

Obviously if you have more than one column, this gets to be quickly unmanageable.

When to change nz() function to COALESCE function?

Coalesce is a feature only available in T-SQL it isn't a VBA function.

It's up to you how you want to handle converting nulls into a new value, there are many ways to do this. You can check your variables before adding them to a SQL statement, or, you can alter them as part of the SQL with Coalesce, IsNull or Case.

Personally, I would do whatever is less code that still gets the idea across clearly. I'd imagine this would be decided on a case by case basis depending what it is you are doing.

Difference of values in the same column ms access sql (mdb)

Consider:

SELECT TOP 10 Table1.ComponentID, 
DCount("*","Table1","ComponentID = 'S3' AND Volume<" & [Volume])+1 AS Seq, Table1.Volume, 
Nz(Table1.Volume -
    (SELECT Top 1 Dup.Volume FROM Table1 AS Dup 
     WHERE Dup.ComponentID = Table1.ComponentID AND Dup.Volume     ORDER BY Volume DESC),0) AS Diff
FROM Table1
WHERE (((Table1.ComponentID)="S3"))
ORDER BY Table1.Volume;

This will likely perform very slowly with large dataset.

Alternative solutions:

1. build query that calculates difference, use that query as source for a report, use textbox RunningSum property to calculate sequence number

2. VBA looping through recordset and saving results to a 'temp' table

3. export to Excel

---

Related Topics