Hi Tan,
First..I assume you are just testing
and would never use a reserved word
"Value" for a table name.
But I do not believe that is the problem....
I believe it is choking on the double brackets
around UL Value.
Change your field name to "UL_Value"
and don't use brackets in the function call
XPercentile("UL_Value","Value",.25)
or eliminate the brackets in the function
'*** code w/o brackets ****
Public Function XPercentile(FName As String, _
TName As String, _
X As Double) _
As Double
' FName = Field name
' TName = Table name
' x = decimal percentile (0.68 for 68%)
' Return the minimum value for which x% of
' the values are lower or equal to it
XPercentile = DMin(FName, TName, _
"DCount(""*"", """ & TName & """, """ & FName & _
"<="" & " & FName & " ) >= " & _
X * DCount("*", TName))
End Function
'*** end code w/o brackets ***
XPercentile("[UL Value]","Value",.25)
Please respond back if I have misunderstood.
Good luck,
Gary Walter
tansh said:
Hi,
Can I check if the solution works for you? I have tried to
to implement it but I keep running into this error:
Run-time error '3075':
Syntax error in query expression 'DCount
("*", "Value", "[UL Value]<=" & [[UL Value]]) >= 818.73'
The table name is "Value" while the Field Name is "UL
Value"
Please help as I need a solution ASAP. Thanks.
Tan
-----Original Message-----
calculate percentile. This is the
Excel function but does not work in Access:
Hi Pat,
On the MVP org site Michel breaks
the definition of Percentile down
into DMin and DCounts, then provides
a function using them that you can save in
a code module, then use in your query.
http://www.mvps.org/access/queries/qry0019.htm
Good luck,
Gary Walter
.