Remvoving Null Items from an ADO Recordset prior to sending to Exc

  • Thread starter Thread starter Alphapumpkin
  • Start date Start date
A

Alphapumpkin

Hi all!

Long time reader, first time poster! There's so much great info in here, I
never had the need to post my own question, some else has already been
stumped!

So I'm passing info from my Access db into Excel to take advantage of the
PERCENTILE function. I do, however, have a few null fields in some of my
columns.

So here's what I started with:

Public Function Percentile(strTbl As String, strFld As String, k As Double)
As Double
Dim rst As ADODB.Recordset
Dim dblData() As Double
Dim xl As Object
Dim x As Integer
Set xl = CreateObject("Excel.Application")
Set rst = New ADODB.Recordset
rst.Open "Select * from " & strTbl, CurrentProject.Connection, adOpenStatic
ReDim dblData(rst.RecordCount - 1)
For x = 0 To (rst.RecordCount - 1)
dblData(x) = rst(strFld)
rst.MoveNext
Next x
Percentile = xl.WorksheetFunction.Percentile(dblData, k)
rst.Close
Set rst = Nothing
Set xl = Nothing
End Function

Without the null values, the above works great!! I was reading on the MS
site about how to search for nulls in an ADO record set.....I just can't seem
to get the syntax right.

I'm thinking I need to switch x to variant type, but even with that, I can't
seem to keep the null values from crashing the PERCENTILE function in Excel.

Is there a way to do what I'm hoping to be able to do in VBA? I'm only a few
weeks into this.....so any guidance would be appreciated! I really want to
be able to open an ADO recordset, filter out the null values, and pass the
info over to excel to use the Percentile function.
 
You can test them in VBA with the IsNull() function. A better solution
would be to simply no retrieve them in the first place:

Dim SQLString
SQLString = Select * from " & strTbl & " Where strFld Is Not Null"

Notice that difference of syntax in SQL.

Also, don't forget that for your case, you must be sure that what you need
might is not to replace the Null values with a 0 (zero) instead of dumping
them away.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)
 
I'm still missing where to put the SQL string - I"m new to manipulating ADO
record sets....

When I insert the proper SQL, I still get "Invalid use of null." Do I need
to declare a new recordset and open that prior to having the array sent to
Excel?
 
Back
Top