Slope Excel Function in an Access query

[jacky]

Hi!

I got a query with 3 fields:

- Team
- Date
- Score

it looks like that:

| Team | Date | Score |
____________________
| red | 02/01/05 | 1 |
| red | 03/01/05 | 2 |
| red | 04/01/05 | 3 |
| red | 05/01/05 | 1 |
| blue | 02/01/05 | 2 |
| blue | 03/01/05 | 3 |
| blue | 04/01/05 | 4 |
| blue | 05/01/05 | 2 |

My goal is to get the Slope of the 'Score' per 'Team' during the last 3
monthes.

I use normally in Excel the Slope function, with as 'Y' the Score and
as 'X' {1;2;3}

My fisrt step was to make a more simple exemple with a table 'tabelle1'
with 2 fields:

- Y (the Score)
- X (the X value)

| Y | X |
_____________
| 2 | 1 |
| 3 | 2 |
| 4 | 3 |
| 2 | 4 |

I wrote this in code:

Public Function Steigung(Y As Double, X As Double)
Dim objExcel As Excel.Application

Set objExcel = New Excel.Application

Steigung = Excel.Application.WorksheetFunction.Slope(Y, X)

objExcel.Quit

Set objExcel = Nothing

End Function

and I made a querry with this expression:

Expr1: Steigung([Tabelle1]![Y];[Tabelle1]![X])

I got always the debugger! Help meeeeeeeeeeeeeeeee!!!

that was my first problem
___________________________________________________________

The second step would be to build for X an array's like (1 to number of
monthes) because I would like to define in an other table the number of
'last monthes' that will be dirfferent for each 'Team'.

Thank's for your help

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

This:

Steigung = Excel.Application.WorksheetFunction.Slope(Y, X)

should be this:

Steigung = objExcel.WorksheetFunction.Slope(Y, X)

For performance improvement you should set the reference to the Excel
object outside the Steigung function. E.g. (untested):

' Declarations section
Dim objExcel As New Excel.Application

Public Function Steigung(Y As Double, X As Double)
Steigung = objExcel.WorksheetFunction.Slope(Y, X)
End Function

Then when the query finishes, remove the objExcel object.

For building an array, you might wish to investigate the Split()
function. Though it only returns strings it may be usable w/ the
implicit conversion of numeric strings to numeric data types. E.g.:

Dim s As String
s = "12"

Dim i As Integer
i = s ' implicit conversion of string to integer

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRJGjPYechKqOuFEgEQICuQCfV6EQ1AlkLnraBfy7n8VBRan1gg0AoOwa
O/8LGhfUm/7hj6lXZyXPl8fy
=IlCK
-----END PGP SIGNATURE-----

 

[jacky]

It's still not working.....-(

But anyaway thank's a lot for help!

 

[jacky]

For people who got the same problem:

http://www.oreilly.com/catalog/accesshks/chapter/hack61.pdf

 

[jacky]

Public Function Steigung(strTbl As String, strFld As String)

Dim rst As ADODB.Recordset
Dim dblData() As Double
Dim dblData2() 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)
ReDim dblData2(rst.RecordCount - 1)

For x = 0 To (rst.RecordCount - 1)
dblData(x) = rst(strFld)
dblData2(x) = x + 1
rst.MoveNext
Next x

Steigung = xl.WorksheetFunction.Slope(dblData, dblData2)

rst.Close
Set rst = Nothing
Set xl = Nothing

End Function