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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
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 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 |
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 :-)
Nz(Null) returns Empty in MS Access, so the following Excel vba matches MS Access perfectly.