Make Table SQL with Percentile parameter (1 Viewer)

[Integrate]

Hi, I am struggling with what may be a simple issue.
I am using a function to calculate the quartiles from an existing query and enter the corresponding EqNum and EDescription into a table.
When I run the code below it asks me for a value for SysAssetCritRankQ from the rstPercentileRST parameters.
I have tried adding "" and [] but they aren't working. Where am I going wrong? (The issue with self taught SQL/VBA)

SysAssetCritQ = Query Name
Total = Calculated field in query

strSQL = "SELECT SysAssetCritRankQ.EqNum, SysAssetCritRankQ.EDescription INTO [EOQComboT]FROM SysAssetCritRankQ " & _
"WHERE Total >= PercentileRst([SysAssetCritRankQ], Total, 0.75); "

PercentileRST function

Public Function PercentileRst(RstName As String, fldName As String, PercentileValue As Double) As Double
   'This function will calculate the percentile of a recordset.
   'The field must be a number value and the percentile has to
   'be between 0 and 1.
   If PercentileValue < 0 Or PercentileValue > 1 Then
      MsgBox "Percentile must be between 0 and 1", vbOKOnly
   End If
   Dim PercentileTemp As Double
   Dim dbs As Database
   Set dbs = CurrentDb
   Dim xVal As Double
   Dim iRec As Long
   Dim i As Long
   Dim RstOrig As Recordset
   Set RstOrig = CurrentDb.OpenRecordset(RstName, dbOpenDynaset)
   RstOrig.Sort = fldName
   Dim RstSorted As Recordset
   Set RstSorted = RstOrig.OpenRecordset()
   RstSorted.MoveLast
   RstSorted.MoveFirst
   xVal = ((RstSorted.RecordCount - 1) * PercentileValue) + 1
   'x now contains the record number we are looking for.
   'Note x may not be     whole number
   iRec = Int(xVal)
   xVal = xVal - iRec
   'i now contains first record to look at and
   'x contains diff to next record
   RstSorted.Move iRec - 1
   PercentileTemp = RstSorted(fldName)
   If xVal > 0 Then
      RstSorted.MoveNext
      PercentileTemp = ((RstSorted(fldName) - PercentileTemp) * xVal) + PercentileTemp
   End If
   RstSorted.Close
   RstOrig.Close
   Set RstSorted = Nothing
   Set RstOrig = Nothing
   Set dbs = Nothing
   PercentileRst = PercentileTemp
End Function

 

[GohDiamond]

The three variables being passed to the Function have to be as defined:
PercentileRst(RstName As String, fldName As String, PercentileValue As Double)

It would help to know what error occurs and When.

Strings are indicated with QUOTATION Marks ""
Have you tried:
PercentileRst("SysAssetCritRankQ", "Total", 0.75)

Cheers!
Goh

 

[Integrate]

I get a Compile Error: Expected: end of statement

I have attached a screen print.

Cheers
Mel

 

[GohDiamond]

Your error generally indicates that the Syntax is not correct, and likely needs adjustment to the Quotation marks in your statement.

Anything that is to be passed as a string needs quotation marks but a SINGLE " in front of your string would indicate to VBA that it had reached the end of the string. Anything after that would not be recognized as valid.

you could use " & chr(34) & SysAssetCritRankQ & chr(34) & "
Or double up on your quotation marks around the String you are providing to the Fuction

Try this:

strSql = "SELECT SysAssetCritRankQ.EqNum, SysAssetCritRankQ.EDescription INTO EOQComboT " & vbCrLf & _
"FROM SysAssetCritRankQ " & vbCrLf & _
"WHERE ((([Total])>=PercentileRst(""SysAssetCritRankQ"",""Total"",0.75)));"

Cheers!
Goh

PS I hate syntax conversion from SQL to VBA

 

[Integrate]

Thank you so much for your time and help. I am sharing your feelings on syntax.

I have tried both of your suggestions and received errors on both.

The first was another runtime 3078 error saying it cannot find the input table or query ". When I click debug it takes me to a line in the PercentileRST Function: Set RstOrig = CurrentDb.OpenRecordset(RstName, dbOpenDynaset)

The second option gives me Invaild use of '.','!', or '()'. in query expression ((([Total])>=PercentileRst(""SysAssetCritRankQ"",""Total"",0 .75)));"

Do you know if maybe there is another way to achieve my end result? I want a combo box in my form to only show the EqNum and description of the products that are in a selected percentile/quartile. The percentile is calculated on the total in the query SysAssetCritRankQ. I tried to do a Make Table query but the table won't allow the calculated column even tho I am using 2013. Maybe I going about it totally wrong...?

 

[Minty]

What does debug.print RstName actually show you?

 

[Integrate]

I can't get anything to come up adding the debug (having a brain fart about where to put it)
But when I hold the mouse over RstName it shows RstName = ""

 

[Minty]

Okay that is probably why it doesn't work then.
Put the debug in here;

Dim RstOrig As Recordset[COLOR="Red"]
debug.print "String is:" RstName[/COLOR]
Set RstOrig = CurrentDb.OpenRecordset(RstName, dbOpenDynaset)

This should help you to see when you do eventually get the syntax to work.

Have you tried putting "'"SysAssetCritRankQ "'" to enclose the name in single quotes?

 

[Integrate]

Yay! Got it working using your solution and holding my tongue the right way...
Thank you so much for you help and patience