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.
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:
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
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
Harald Staff:
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
26 February 2007, 7:48 am