# Thread: excel function in a query, ie Poisson funct. (xp 2003)

1. ## excel function in a query, ie Poisson funct. (xp 2003)

Thanks for all your help in the past

I need to use an excel function in a query, specifically the Poisson function

The query should generate the following result = 1- Poisson (2001-1,mean19972000,true)

Where 2001 is the number of events in the year 2001,
Mean19972000 is the mean number of event from 1997 to 2000,
True is used as the logical function for the cumulative.

This should give me the Poisson probability of 0,022315 if I had 25 events in 2001 and there were a mean number of events of 16 between 1997 an 2000.

Option Compare Database

Function poissonprob(x As Double, mean19972000 As Double, cumulative As Text)
' Uses the Excel function cumpoison to return the cummaltive poison
'
On Error GoTo E_Handle
Dim objXL As Excel.Application
Set objXL = CreateObject("Excel.Application")
objXL.Workbooks.Open (objXL.Application.LibraryPath & "temptest.xla")
objXL.Workbooks("test.xla").RunAutoMacros (xlAutoOpen)
ppoison = objXL.Application.Run(1-poison(2001-1, mean19972000, true)
fExit:
objXL.Quit
Set objXL = Nothing
Exit Function
E_Handle:
MsgBox Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number
Resume fExit
End Function

Richard

2. ## Re: excel function in a query, ie Poisson funct. (xp 2003)

Is there something that doesn't work in what you are trying to do, or is this a working solution to the problem you describe?

3. ## Re: excel function in a query, ie Poisson funct. (xp 2003)

From your description, I get the impression that '2001' is the name of a field in a table or column. The way you use it. it is treated as a number. To use it as a field name, you must enclose it in brackets: [2001]. You should NOT have 2001 or [2001] in the function itself, however, only when calling it. Neither should mean19972000 be in the function.
When you need to use a worksheet function, you must use WorksheetFunction, not Run.
As far as I can see, there is no need to open an add-in as a workbook.
Finally, your function is named poissonprob, but you never assign a value to poissonprob. The value assigned to ppoison isn't going anywhere.

The function becomes simpler:

Function PoissonProb(x As Double, mean As Double)
' Uses the Excel function Poisson to return the cumulative Poisson probability
Dim objXL As Excel.Application
On Error GoTo E_Handle
Set objXL = CreateObject("Excel.Application")
PoissonProb = objXL.WorksheetFunction.Poisson(x, mean, True)

fExit:
objXL.Quit
Set objXL = Nothing
Exit Function

E_Handle:
MsgBox Err.Description, vbCritical, "Error: " & Err.Number
Resume fExit
End Function

and you use it like this in a query:

P: 1-PoissonProb([2001]-1,[mean19972000])

Note: using an Excel function in a query this way will be <big>EXTREMELY</big> inefficient, since you are starting and quitting an instance of Excel for each value being calculated.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•