Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2008
    Posts
    2

    Unanswered: NORMSDIST in Access VB code

    I'm having issues calling the NORMSDIST function from Excel in Access. Could someone provide me with the code for calling this function. I'm attempting to calculate the normal distribution of a z-score.
    My z-score field is called BMIzscore and I need the NORMSDIST function to run on [BMIzscore] and put the result in [BMIpercentile].

    I attempted to initiate the code for a couple of hours yesterday, but no luck. Any instruction would be very much appreciated.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    it's one of those simple-but-ugly things:

    dim oXL as excel.application
    set oXL = createobject("excel.application")
    me.myOutBox = oXL.application.normsdist(me.myInBox)
    'me.myOutBox = oXL.normsdist(me.myInBox) 'this also works, by the way
    set oXL = nothing

    ...and instantiating Excel takes time: if you are doing more than one of these, keep oXL alive until you have finished.

    above example is early-bind so you will need a reference to Excel (and thus become Excel-version dependent)
    no reason why it shouldn't work late-bind, but it is already slower than you would like.

    izy
    Last edited by izyrider; 08-27-08 at 13:56.
    currently using SS 2008R2

  3. #3
    Join Date
    Aug 2008
    Posts
    2
    Thanks for the code.

    Is there something I have to do different in order to have it run the function in a query?
    Better yet...where do put the code?

    I opened the visual basic editor, and typed in what I thought would initiate the function, but I must have it in the wrong place.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    first you need a reference to Excel:
    Alt-F11 (get the code window)
    menu: Tools/References
    ...scroll on down until you find Excel (under M for Microsoft).
    select it.
    OK

    to check it works: a form, two textboxes (myInBox, myOutBox) and a button. add above code to button _Click() event.
    place a value in myInBox and hit the button.

    you can use any function you can dream up in a query - it is not the fastest experience in the world. the function *MUST* be public in a module. your query will take all day if you instantiate Excel for each row, so the game plan is to keep oXL alive while the query runs.

    create a module.

    (early-morning aircode: may contain errors)

    option explicit

    dim oXL as Excel.Application 'module-wide declaration

    public sub initNormSDist 'initiate oXL
    set oXL = createobject("Excel.Application")
    end sub

    public sub termNormSDist 'destroy oXL
    on error resume next
    set oXL = nothing
    end sub

    public function doNormSDist(inVal as double) as double
    doNormSDist = oXL.NormSDist(inVal)
    end function

    assuming you have an update query myUpdate that reads something like:
    UPDATE myTable SET bmipercentile = doNormSDist([bmizscore])

    then the following might just work:
    yada
    yada
    yada
    initNormSDist
    currentdb.execute "myUpdate"
    termNormSDist
    yada
    yada
    yada

    i.e.
    first use init to instantiate Excel
    run your query
    finally use term to kill Excel

    izy
    currently using SS 2008R2

Posting Permissions

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