Percentile code - Module Collection Error (1 Viewer)

[sha7jpm]

Hi everyone, not been posting for a while, but lo and behold upon my return to access I hit a problem!

I am trying to run some code which I found on this site (very kind of the provider, many thanks)
& am having problems making it work with my database..

I am getting an error "Item not in collection" I know this refers to an unknown/wrong data field but I cannot find any wrongly named source in my code.

any ideas? or "look you fool the error is obvious" type answer would be fine also!

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Option Compare Database


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


Set dbs = CurrentDb

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

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

SQL = "SELECT Distinct [October_2003_English_Average_Points_Score] FROM [SEN_pilot_English_by_school]"
Set rstOctscorefirst = dbs.OpenRecordset(SQL, dbOpenForwardOnly)

Do While Not rstOctscorefirst.EOF
SQL = "SELECT October_2003_English_Average_Points_Score" & _
" FROM SEN_pilot_English_by_school" & _
" WHERE October_2003_English_Average_Points_Score = Cint('" & rstOctscorefirst![October_2003_English_Average_Points_Score] & "')"

Set rstOctscore = dbs.OpenRecordset(SQL)

rstOctscore.MoveLast
rstOctscore.MoveFirst

' Pass scores from recordset to array.
arrayOctscore = rstOctscore.GetRows(rstOctscore.RecordCount)
rstOctscore.Close

With rstPercentile
.AddNew
![October_2003_English_Average_Points_Score] = rstOctscorefirst![October_2003_English_Average_Points_Score]

'--- Pass array to Excel Percentile function.
![Percentile] = WorksheetFunction.Percentile(arrayOctscore, 0.25)
.Update
End With

rstOctscorefirst.MoveNext
Loop

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

MsgBox "Updated table Percentile."

End Sub

 

[WayneRyan]

sha,

Which line is giving the error?

Are those really underscores in the names? Or should they be spaces?

Wayne

 

[sha7jpm]

cheers!

Hi Wayne,

thanks for the reply,

they are underscores - we link most db's to oracle and therefore we need to avoid using spaces. does that cause a problem in the code?

the line which has the glitch is

With rstPercentile
.AddNew
![October_2003_English_Average_Points_Score] = rstOctscorefirst![October_2003_English_Average_Points_Score]

ta

John

 

[cable]

you've checked the spelling in tables SEN_pilot_English_by_school and Percentile?

do you need the [] for the fields in the addnew code??? also try ("fieldname") instead of the !/bang.

 

[sha7jpm]

discovery!

found the problem!

I now have a red face, the percentile table had average point score, rather than points score.

problem solved

many thanks for your postings.. apols again.

John

 

[deepthiv]

i am using Excel Object API in VB.NET

Dim X1 As Object

X1 = CreateObject("Excel.Application")

Dim res As Double = X1.WorkSheetFunction.Percentile(dblData,p)

here is dblData is array of type double which has recordset column values,

There is an exception saying. "Unable to get the Percentile property of the WorksheetFunction class"

i have added "Microsoft Excel 9.0 Object Library"

am i missing anything? Can some one please help

Regards,
Deepthi