Nz Function

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 = “”
                Case Else
                    Nz = 0
            End Select
        Else
            Nz = vDefault
        End If
    Else
        Nz = fldTest.Value
    End If
   
End Function

See W3Schools List of ADO Type Property Constants

Here’s some code I used to test it:

Sub Test()
   
    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 FilesMicrosoft Office 2003”
    sConn = sConn & “OFFICE11SAMPLESNorthwind.mdb;DefaultDir=C:Program Files”
    sConn = sConn & “Microsoft Office 2003OFFICE11SAMPLES;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
    cn.Open sConn
   
    Set rs = New ADODB.Recordset
    rs.Open sqlOrders, cn
   
    With rs
        .MoveFirst
        sRegion = Nz(rs.Fields(“ShipRegion”))
        dFreight = Nz(rs.Fields(“Freight”))
    End With
   
    Debug.Print sRegion
    Debug.Print dFreight
   
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
       
End Sub
Posted in Uncategorized

One thought on “Nz Function

  1. I must admit that i use On Error Resume Next while getting values from recordsets, like

    On Error Resume Next
    MyItem.LngID = Rst(0)
    MyItem.StrName = Rst(1)
    ‘…
    On Error Goto 0
    Rst.MoveNext

    Then numerical properties keeps the default 0 and strings “” on null errors. …It’s ok to call me ignorant and lazy in this thread :-)

Leave a Reply

Your email address will not be published. Required fields are marked *