Contact Us
Site Map

Search

VBScript doesn't support the NZ() function. But here is a custom implementation, and a demonstration of its use: 

<%      Function NZ(ValueIfNotNull, ValueIfNull)          NZ = ValueIfNotNull          If (IsNull(NZ)) Then NZ = ValueIfNull      End Function        str1 = "foo"      Response.Write NZ(str1, "bar")        str2 = NULL      Response.Write NZ(str2, "bar")  %>

 
A more common place you would expect to use NZ(), however, is returning the results of an Access query. You will find that if you try to use NZ() and call the query from an ASP page, you'll get this error: 
 

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'   [Microsoft][ODBC Microsoft Access Driver] Undefined function 'NZ' in expression.

 
Let's say you have a numeric column, and want it to return 0 instead of NULL for all records that are NULL. 
 
In Access, you would say: 
 

SELECT ColumnValue =       NZ(Column, 0)      FROM table ...

 
Since you can't use NZ() from ODBC/OLE-DB, you can use IIF instead: 
 

SELECT ColumnValue =      IIF(ISNULL([Column]), 0, [Column])      FROM table ...

 
Or you can try IFNULL if you are going through ODBC, e.g. 
 

SELECT ColumnValue =      IFNULL(Column, 0)      FROM table ...

 
If you are porting this code to SQL Server, you would use slightly different syntax: 
 

SELECT ColumnValue =      COALESCE(Column, 0)      FROM table ...

 
or 
 

SELECT ColumnValue =      ISNULL(Column, 0)      FROM table ...

 
(I prefer the COALESCE expression, which accepts multiple successive arguments. So you could say COALESCE(col1, col2, col3, ...) and it would return the *first* column that is NOT NULL.) 
 
Of course if NULL means the same thing as 0, you should consider running this query: 
 

UPDATE column SET column = 0 WHERE column IS NULL

 
For more information on preventing NULLs from entering your ASP page, or avoiding NULLs in your data altogether, see Article 2150 and Article #2073.