Brad:
I don't know the context in which you are performing this query, however,
let me make a few comments. You can call a custom VBA function in Access
from a query. For example:
SELECT MyFunction([InputValueField])
FROM MyTable
Second, you can call Excel functions through the WorksheetFunction object.
See the following MSDN article for more specifics on this object.
http://msdn.microsoft.com/library/d...l/xlhowUsingWorksheetFunctions_HV05205786.asp
Given these two points, you can create a wrapper function for the Excel
NormDist function to call it in an Access query. I don't know how large
your query is (number of records), so I have shown two approaches. The
first is somewhat slow as it has to start an instance of Excel for each
record to run the calculation. For a small query this may be acceptable, or
a large query may be not.
Function CallNormDist(dblInput As Double, dblMean As Double, dblSD As
Double, bCumulative As Boolean) As Double
Dim xl As New Excel.Application
CallNormDist = xl.WorksheetFunction.NormDist(dblInput, dblMean, dblSD,
bCumulative)
Set xl = Nothing
End Function
The second approach improves performance by making the Excel instance static
over repeated calls to the function, however, it leaves this instance of
Excel open until the function is called again with a flag to close the
instance. To close the instance, a post-query call to the function is
necessary. Again, depending on the context with which you are running the
query, this may or may not be an issue. For example, if you are using the
DoCmd.OpenQuery method, you can make a separate call to the function after
calling this method. You could also use the query in a report and then make
a separate call to the function in the report's Close event (or possibly
another event) to close the instance of Excel created by the query. In all
cases, the instance of Excel is invisible to the user.
Function CallNormDist(dblInput As Double, dblMean As Double, dblSD As
Double, bCumulative As Boolean, bEndExcel As Boolean) As Double
Static xl As New Excel.Application
CallNormDist= xl.WorksheetFunction.NormDist(dblInput, dblMean, dblSD,
bCumulative)
If bEndExcel Then Set xl = Nothing
End Function
--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com
This response is supplied "as is" without any representations or warranties.
Hi,
Anyone know how to do the equivalent of the excel NORMDIST function in an
access query? That is: to convert a z score into a cumulative percentile.
Your comments welcome.
