I need an Nz function in Excel similar to Access’s Nz function. I have a class with a bunch of strongly typed properties, e.g. a TxnID property that’s typed as a String rather than a Variant. I’m creating a recordset from an Jet database and putting the recordset’s fields into the class. Some of the fields are Null and that doesn’t play well with my strongly typed properties. Specifically, I get an “Invalid Use of Null”.
In Access, it seems that Nz returns a zero-length string when passed a string and a zero when passed anything else. At first, I had an argument called bIsString and used it to determine whether to return a zero-length string or a zero. I didn’t really like that method because it was too easy to forget and it didn’t match Access’s Nz function, which figured it out on its own. Instead of passing a Variant value (that could be Null), I switched to passing the ADODB.Field object. That way I could test the Type property to determine what kind of return value would be appropriate.
This is what I came up with. I haven’t tested the heck out of it, so let me know if you note any mistakes.
Function Nz(fldTest As ADODB.Field, _
Optional vDefault As Variant) As Variant
If IsNull(fldTest.Value) Then
If IsMissing(vDefault) Then
Select Case fldTest.Type
Case adBSTR, adGUID, adChar, adWChar, adVarChar, adVarWChar
Nz = ""
Nz = 0
Nz = vDefault
Nz = fldTest.Value
Here’s some code I used to test it:
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConn As String
Dim sqlOrders As String
Dim sRegion As String
Dim dFreight As Double
sConn = "DSN=MS Access Database;DBQ=C:\Program Files\Microsoft Office 2003\"
sConn = sConn & "OFFICE11\SAMPLES\Northwind.mdb;DefaultDir=C:\Program Files\"
sConn = sConn & "Microsoft Office 2003\OFFICE11\SAMPLES;DriverId=25;FIL=MS "
sConn = sConn & "Access;MaxBufferSize=2048;PageTimeout=5;"
sqlOrders = "SELECT CustomerID, EmployeeID, Freight, OrderDate, ShipRegion FROM Orders"
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
rs.Open sqlOrders, cn
sRegion = Nz(rs.Fields("ShipRegion"))
dFreight = Nz(rs.Fields("Freight"))
Set rs = Nothing
Set cn = Nothing