Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011
    Posts
    2

    Unanswered: Need normsdist code for Access

    I use the wizards quite a bit in MS Access but am completely unfamililiar with module code. I have fields [zscore1], [zscore2], [zscore3] in Tbl:InitialData. I need to calculate [%ile1], [%ile2], [%ile3].

    I've already made the reference connection to Excel. I just need to know exactly what to type in the module portion - and how to execute. Thanks!

    (working in MS 2003)
    Last edited by lp7981; 06-01-11 at 16:02.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    What are you trying to calculate, and where?

    Also, if there's an Excel workbook function that gives you the answer you need, there will most likely be a native VBA version that you can use instead.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Jun 2011
    Posts
    2

    Calculating Percentile from Z-score

    I'm trying to calucate the percentile from the z-score. In excel this is =normsdist(z-score)*100 gives me the percent. I have the z-score calculated, just need to know how to write the VBA code to execute this excel funtion and then how do I execute the VBA code?

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    To use an Excel workbook function in Access, you'd need to set up a reference to the Excel library. Once you've done that, you invoke the function as follows:
    Code:
    variable = Excel.Application.WorksheetFunction.NormDist([arguments])
    According to the pop-up thing, there are four arguments for this function, although it doesn't indicate which (if any) are optional.

    The easiest way to use it would be to encapsulate it in a custom function and use the function in a query. Please note that queries that use lots of custom functions run really slowly.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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