urgent help for build sql statement in vba

  • Thread starter Thread starter terry
  • Start date Start date
T

terry

My assignment's due date is approaching. I'm nervous now!
How can I build this sql statement in a function:
SELECT LAST(fieldName)
FROM (SELECT TOP p PERCENT fieldName
FROM tblName
WHERE fieldName is not null
ORDER BY fieldName);

fieldName, tblName, p are input variables of the function.

Thanks a lot!

Mine is: Set ssMedian = MedianDB.OpenRecordset
("select last ( [" & fldName & "] ) from (SELECT TOP [ "
& p & "] percent [" & fldName & _
"] FROM [" & tName & "] WHERE [" & fldName & _
"] IS NOT NULL ORDER BY [" & fldName & "]);")
i got some error complaining this sql.
 
Try assigning the SQL to a string so you can examine what is built for any error
in syntax

Dim strSQL as String

StrSQL = "SELECT LAST([" & fldName & "])"
StrSQL = StrSQL & " FROM (SELECT TOP " & p & " Percent " ...

That way you can use a

Debug.Print strSQL

or

MSGBOX StrSQL

statement to examine the string you have built and see what the error is. You
can even copy and paste the string into a query and see what error you get.
 
Back
Top