Access Error 3075: Unknown

[AccessNewbi]

Hi, very new to Access and am having trouble with custom XPercentile code (pulled from internet, credit Michel Walsh).

I'm using XPercentile successfully here:

=XPercentile("nmr","Control NMR",0.75)-XPercentile("nmr","Control NMR",0.25)

Control NMR query:

SELECT [NMR Distribution].Arm, [NMR Distribution].nmr, [NMR Distribution].Record_ID
FROM [NMR Distribution]
GROUP BY [NMR Distribution].Arm, [NMR Distribution].nmr, [NMR Distribution].Record_ID
HAVING ((([NMR Distribution].Arm)=0));

But the same is not working here:

=XPercentile("nmr","Control NMR No Outliers",0.75)-XPercentile("nmr","Control NMR No Outliers",0.25)

- not working

Control NMR No Outliers:

SELECT [NMR Distribution].Arm, [NMR Distribution].nmr, [NMR Distribution].Record_ID
FROM [NMR Distribution]
GROUP BY [NMR Distribution].Arm, [NMR Distribution].nmr, [NMR Distribution].Record_ID
HAVING ((([NMR Distribution].Arm)=0) AND (([NMR Distribution].nmr)<3));

For reference, this is the raw XPercentile code:

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

The only difference in the queries is the restriction to values below 3, but this appears to cause a problem and I'm not sure why. Any help is appreciated, please let me know if more information is needed and I'll try my best!

[Micron]

Likely you will have to post a zipped copy of your db as just reading that doesn't reveal much - especially because "not working" is not helping. That means what - no results? Wrong results? Error message? Code won't compile? You're saying it works if the all the field values are less than or equal to 3?

Best to post code and sql within code tags (use # on posting toolbar) to maintain indentation and make it easier to read. If not, you will also get spaces inserted every 50 characters, which often makes responders question your code.

[AccessNewbi]

Hi, my first time here, so thank you much for the tips! I've edited the post to contain the code tags. My db is for research and contains participant data, so I'm hesitant to share the full thing, hopefully I can add some clarity here.

This code is intended to use XPercentile to calculate the 75th and 25th percentile to get Interquartile Range for numeric "nmr" values. The original "Control NMR" query contains all nmr values for the control arm of our project, as well as the record ID and arm information (which I include just to help me verify the accuracy of the query). "Working" here means that the code runs and correctly calculates the percentiles, then correctly subtracts them to present a numeric IQR (0.504 in this case).

The "Control NMR No Outliers" query is a copy of the original, but excludes any nmr value that is above 3 (which we consider to be outlier values). When running the same percentile and IQR calculations on this query, I receive the error "Run-time Error 3075: Unknown". In searching for solutions, I see the 3075 is a "missing operator" but I am having difficulty identifying where that might be, especially because the only difference between the 2 is here:

HAVING ((([NMR Distribution].Arm)=0));

vs.

HAVING ((([NMR Distribution].Arm)=0) AND (([NMR Distribution].nmr)<3));

[Micron]

The subject matter is beyond my expertise so about all I can do is suggest that you perform basic trouble shooting methods. If the problem lies within sql, try
- removing components until it works and build it back in stages until it doesn't. That might id the problem part.
- swap variable and/or function calls with valid values and test. Don't forget that text values and dates require delimiters ("text", #01/01/2022#).
I'm going to try reposting your procedure because there's no way you should have gotten such a shallow window there.
As for the db, consider creating a copy with only enough parts to replicate the issue.

Also, there is a randomizing function here that you might be able to make use of for this purpose:
https://www.accessforums.net/showthread.php?t=77482

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

In the meantime, I'll look further at the sql later to see if I can spot anything. Quite possibly it is due to text or date values not being delimited, or perhaps some values in records somewhere are null but I lean towards the former.

[Welshgasman]

Why is the code window for the function so small?
Like looking through a letterbox to view a house.
As you have spaces in object names, I would surround them with []