Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Joliette, Quebec, Canada
    Posts
    290
    Thanks
    9
    Thanked 0 Times in 0 Posts

    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. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    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?
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •