Percentile Function -1004 - Unable to access worksheet error!

[sha7jpm]

Hi all,

I ran this code last year with no problems. This year I have used the code again in exactly the same state and it does not run.
my error msg is "1004 - Unable to get the Percentile property of the WorkSheetFunction Class" I have highlighed the code in red where this occurs.

I assume this is about the linking with Excel.
I have in my references the following ticked -
VBA
Access library 9.0
OLE Automation
MS DAO 3.6
MS Excel 9.0 library
MS ADO (multi dimensional) 2.7

Any ideas on how to fix this?

many thanks

John

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

Private Sub cmdUpdate_03_05_Click()
Dim dbs As DAO.Database
Dim rstPercentile As DAO.Recordset
Dim rstOctscore As DAO.Recordset
Dim rstJunescore As DAO.Recordset
Dim SQL As String
Dim arrayJunescore

'**************************
'IL

Set dbs = CurrentDb

SQL = "DELETE * FROM [03_05_CountyLine_IL_Percentile]"
dbs.Execute SQL

Set rstPercentile = dbs.OpenRecordset("03_05_CountyLine_IL_Percentile", dbOpenDynaset)

SQL = "SELECT Distinct [October_2003_IL_Average_Points_Score] FROM [03_05_CountyLine_IL_Percentile_query]"
Set rstOctscore = dbs.OpenRecordset(SQL, dbOpenForwardOnly)

Do While Not rstOctscore.EOF
SQL = "SELECT June_2005_IL_Average_Points_Score" & _
" FROM 03_05_CountyLine_IL_Percentile_query" & _
" WHERE October_2003_IL_Average_Points_Score = Cint('" & rstOctscore![October_2003_IL_Average_Points_Score] & "')"

Set rstJunescore = dbs.OpenRecordset(SQL)

rstJunescore.MoveLast
rstJunescore.MoveFirst

' Pass scores from recordset to array.
arrayJunescore = rstJunescore.GetRows(rstJunescore.RecordCount)
rstJunescore.Close

With rstPercentile
.AddNew
![October_2003_IL_Average_Points_Score] = rstOctscore![October_2003_IL_Average_Points_Score]

'--- Pass array to Excel Percentile function.
![25%_VA_Lower] = xl.WorksheetFunction.Percentile(arrayJunescore, 0.25)
![50%_VA_Median] = xl.WorksheetFunction.Percentile(arrayJunescore, 0.5)
![75%_VA_Upper] = xl.WorksheetFunction.Percentile(arrayJunescore, 0.75)
.Update
End With

rstOctscore.MoveNext
Loop

Set rstPercentile = Nothing
Set rstOctscore = Nothing
Set rstOctscore = Nothing
Set dbs = Nothing

MsgBox "Updated table with 25% ,50%, 75% Percentiles for IL."

End Sub

 

[sha7jpm]

follow-up

please ignore the xl. text within the percentile lines.

this was a failed attempt at fixing it and had not deleted it from the code before posting.

J

 

[Bat17]

Calling WorksheetFunction.Percentile works OK for me. The only thing I can think of is trying the function in XL direct to make sure that it is not an XL problem.

Peter

 

[sha7jpm]

thanks peter,

i get the feeling it is a machine setup somewhere.

especially when the same code worked absolutely fine last year on my previous machine. I am going to try it on my laptop and see if it runs ok there.

it is so annoying not to be able to pinpoint the glitch.

thanks again

john

 

[sha7jpm]

help!

arrggh...

I have found another percentile code on this forum and downloaded the db (thanks to jon K) and it works fine on my machine.

the references are the same as my code but my code does not run..

any ideas as to why?

the code that works is -

Private Sub cmdCalculate_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim arrayNum

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT [Num] FROM [Vertical Data]" & _
" WHERE [Num] IS NOT NULL")

rs.MoveLast
rs.MoveFirst
arrayNum = rs.GetRows(rs.RecordCount)

Me.txtMedian = WorksheetFunction.Median(arrayNum)
Me.txtPercentile = WorksheetFunction.Percentile(arrayNum, 0.3)

Set db = Nothing
Set rs = Nothing

End Sub


************* my code is ---------



Private Sub cmdUpdate_Click()
Dim dbs As DAO.Database
Dim rstPercentile As DAO.Recordset
Dim rstOctscore As DAO.Recordset
Dim rstJunescore As DAO.Recordset
Dim SQL As String
Dim arrayJunescore






'**************************
'IL

Set dbs = CurrentDb

SQL = "DELETE * FROM [CountyLine_IL_Percentile]"
dbs.Execute SQL

Set rstPercentile = dbs.OpenRecordset("CountyLine_IL_Percentile", dbOpenDynaset)

SQL = "SELECT Distinct [October_2003_IL_Average_Points_Score] FROM [CountyLine_IL_Percentile_query]"
Set rstOctscore = dbs.OpenRecordset(SQL, dbOpenForwardOnly)

Do While Not rstOctscore.EOF
SQL = "SELECT June_2004_IL_Average_Points_Score" & _
" FROM CountyLine_IL_Percentile_query" & _
" WHERE October_2003_IL_Average_Points_Score = Cint('" & rstOctscore![October_2003_IL_Average_Points_Score] & "')"

Set rstJunescore = dbs.OpenRecordset(SQL)

rstJunescore.MoveLast
rstJunescore.MoveFirst

' Pass scores from recordset to array.
arrayJunescore = rstJunescore.GetRows(rstJunescore.RecordCount)
rstJunescore.Close

With rstPercentile
.AddNew
![October_2003_IL_Average_Points_Score] = rstOctscore![October_2003_IL_Average_Points_Score]

'--- Pass array to Excel Percentile function.
![25%_VA_Lower] = WorksheetFunction.Percentile(arrayJunescore, 0.25)
![50%_VA_Median] = WorksheetFunction.Percentile(arrayJunescore, 0.5)
![75%_VA_Upper] = WorksheetFunction.Percentile(arrayJunescore, 0.75)
.Update
End With

rstOctscore.MoveNext
Loop

Set rstPercentile = Nothing
Set rstOctscore = Nothing
Set rstOctscore = Nothing
Set dbs = Nothing

MsgBox "Updated table with 25% ,50%, 75% Percentiles for IL."

End Sub

 

[Bat17]

It looks as if arrayJunescore is retuning invalid data.
try:-
MsgBox arrayJunescore
before calling the function to check the value.

Peter