Friday, August 28, 2015

Excel does not like Access Nz() function

If you happened to hook up an Excel report to retrieve data from Access and one day your Excel refuse to download the data, have look on the queries in Access that are using Nz() function.

To whoever does not know what Nz() function is, it is used for null value checking and if true, the second argument of the function will be returned. This function is great formatting result, calculation and etc.

To overcome this issue, my solution was to replace Nz() with IIF() function (e.g. IIF(field1 is null, 0, field1) means if field1 is null, return 0 else return field1)). Once I got all the Nz() function replaced, everything went back to normal. Amazing...

The version of Excel and Access for my case is 2007. Hope this helps whoever use Access query with Nz() and suddenly, Excel stops retrieve data from the query.