Attempting to create a function that i can use in a query.

[Guest]

Hi,

I would like a wee bit of assistance in attempting to use this code that I
have found on the net.

The code is:

Public Function NormSInv(ByVal p As Double) As Double
Const a1 = -39.6968302866538, a2 = 220.946098424521, a3 = -275.928510446969
Const a4 = 138.357751867269, a5 = -30.6647980661472, a6 = 2.50662827745924
Const b1 = -54.4760987982241, b2 = 161.585836858041, b3 = -155.698979859887
Const b4 = 66.8013118877197, b5 = -13.2806815528857, c1 =
-7.78489400243029E-03
Const c2 = -0.322396458041136, c3 = -2.40075827716184, c4 =
-2.54973253934373
Const c5 = 4.37466414146497, c6 = 2.93816398269878, d1 =
7.78469570904146E-03
Const d2 = 0.32246712907004, d3 = 2.445134137143, d4 = 3.75440866190742
Const p_low = 0.02425, p_high = 1 - p_low
Dim q As Double, r As Double
If p < 0 Or p > 1 Then
Err.Raise vbObjectError, , "NormSInv: Argument out of range."
ElseIf p < p_low Then
q = Sqr(-2 * Log(p))
NormSInv = (((((c1 * q + c2) * q + c3) * q + c4) * q + c5) * q + c6) / _
((((d1 * q + d2) * q + d3) * q + d4) * q + 1)
ElseIf p <= p_high Then
q = p - 0.5: r = q * q
NormSInv = (((((a1 * r + a2) * r + a3) * r + a4) * r + a5) * r + a6) *
q / _
(((((b1 * r + b2) * r + b3) * r + b4) * r + b5) * r + 1)
Else
q = Sqr(-2 * Log(1 - p))
NormSInv = -(((((c1 * q + c2) * q + c3) * q + c4) * q + c5) * q + c6)
/ _
((((d1 * q + d2) * q + d3) * q + d4) * q + 1)
End If
End Function

I have entered and saved this in a module and then tried to use it in a
standard select query yet when I try to run the query I get the error message
"Undefined function "NormSInv" in expression.

As you can probably gather I am new this part of access and would gratefully
appreciate any assistance in resolving this issue.

Thanks in advance.

 

[RoyVidar]

Hi,

I would like a wee bit of assistance in attempting to use this code
that I have found on the net.

The code is:

Public Function NormSInv(ByVal p As Double) As Double
Const a1 = -39.6968302866538, a2 = 220.946098424521, a3 =
-275.928510446969 Const a4 = 138.357751867269, a5 =
-30.6647980661472, a6 = 2.50662827745924 Const b1 =
-54.4760987982241, b2 = 161.585836858041, b3 = -155.698979859887
Const b4 = 66.8013118877197, b5 = -13.2806815528857, c1 =
-7.78489400243029E-03 Const c2 = -0.322396458041136, c3 =
-2.40075827716184, c4 = -2.54973253934373
Const c5 = 4.37466414146497, c6 = 2.93816398269878, d1 =
7.78469570904146E-03
Const d2 = 0.32246712907004, d3 = 2.445134137143, d4 =
3.75440866190742 Const p_low = 0.02425, p_high = 1 - p_low
Dim q As Double, r As Double
If p < 0 Or p > 1 Then
Err.Raise vbObjectError, , "NormSInv: Argument out of range."
ElseIf p < p_low Then
q = Sqr(-2 * Log(p))
NormSInv = (((((c1 * q + c2) * q + c3) * q + c4) * q + c5) * q
+ c6) / _ ((((d1 * q + d2) * q + d3) * q + d4) * q + 1)
ElseIf p <= p_high Then
q = p - 0.5: r = q * q
NormSInv = (((((a1 * r + a2) * r + a3) * r + a4) * r + a5) * r
+ a6) * q / _
(((((b1 * r + b2) * r + b3) * r + b4) * r + b5) * r + 1)
Else
q = Sqr(-2 * Log(1 - p))
NormSInv = -(((((c1 * q + c2) * q + c3) * q + c4) * q + c5) * q
+ c6) / _
((((d1 * q + d2) * q + d3) * q + d4) * q + 1)
End If
End Function

I have entered and saved this in a module and then tried to use it in
a standard select query yet when I try to run the query I get the
error message "Undefined function "NormSInv" in expression.

As you can probably gather I am new this part of access and would
gratefully appreciate any assistance in resolving this issue.

Thanks in advance.

What kind of module did you paste this into? It need to be a standard
module - in VBE - Insert | Module

I'm guessing you may have pasted it into a forms or reports class
module?