SQL Null set to Zero for adding

Question

I have a SQL query (MS Access) and I need to add two columns, either of which may be null. For instance:

SELECT Column1, Column2, Column3+Column4 AS [Added Values]
FROM Table

where Column3 or Column4 may be null. In this case, I want null to be considered zero (so 4 + null = 4, null + null = 0).

Any suggestions as to how to accomplish this?

Answer 1

Since ISNULL in Access is a boolean function (one parameter), use it like this:

SELECT Column1, Column2, IIF(ISNULL(Column3),0,Column3) + IIF(ISNULL(Column4),0,Column4) AS [Added Values]
FROM Table

Answer 2

Use the ISNULL replacement command:

 SELECT Column1, Column2, ISNULL(Column3, 0) + ISNULL(Column4, 0) AS [Added Values]FROM Table

Answer 3

Use COALESCE.

SELECT 
   Column1, 
   Column2, 
   COALESCE(Column3, 0) + COALESCE(Column4, 0) AS [Added Values]
FROM Table

Answer 4

Even cleaner would be the nz function

nz (column3, 0)

Answer 5

The Nz() function from VBA can be used in your MS Access query.

This function substitute a NULL for the value in the given parameter.

SELECT Column1, Column2, Nz(Column3, 0) + Nz(Column4, 0) AS [Added Values]
FROM Table