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

Question

I have a project that can use either SQL Server or MS Access as the data store. In one SELECT statement, I must perform a COALESCE operation on a single column and a single value, like this:

SELECT COALESCE([Amount], 0) FROM PaymentsDue;

I would like to write a single SQL statement that will execute correctly in both SQL Server and MS Access. The SQL Server version that is of immediate interest is 2008, although a solution applicable across versions would be preferred.

Earlier today, someone was able to show me an SQL trick that allowed me to use a single SELECT statement to effectively CAST a DATETIME to DATE. I was wondering if anyone has a similar trick to perform a COALESCE (eg, IFNULL or NZ) operation in a way that can be applied to both SQL Server and MS Access?

Answer 1

Create a custom public function in a module.

Public Function COALESCE(InputValue, ValueIfNull)
   COALESCE = nz(InputValue, ValueIfNull)
End Function

Add in error handling, etc., make improvements.

Now, you would be able to use the COALESCE function in MS Access and SQL.

Answer 2

I don't think there is any syntax that functions the same on both platforms.

Note Nz() is only available when using the Access user interface.

Here are a couple of suggestions that can be transformed to COALESCE fairly easily, though repeating the column is a pain:

Sample 1:

SELECT IIF([Amount] IS NULL, 0, [Amount]) FROM PaymentsDue;

Sample 2:

SELECT SWITCH([Amount] IS NULL, 0, TRUE, [Amount]) FROM PaymentsDue;