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!

****************

Still lost! I tried to add all sorts of code to this to not count nulls or include nulls in an array.......I think I could figure it out if I want trying to pass the data into an array. I thought this would be really easy if I could work in strCriteria and look for values <> null, add to array and keep going until EOF and send open excel and run the percentile function......really pulling my hair out over this one.