-
I'm trying to use a select top 10 or 25 or 50 T-SQL in VB...This code I wrote doesn't seem to do the job..can any experts help ..
sSQL = "Select Top " & Parameters("sReportValue") & ""
sSQL = sSQL & "DocumentName, HitCount "
sSQL = sSQL & "from RollupDocument, RollupLookupDocument "
sSQL = sSQL & "Where RollupType=" & iRollupType
sSQL = sSQL & " and RollupYear=" & iRollupYear
sSQL = sSQL & " and RollupValue=" & iRollupValue
sSQL = sSQL & " Order by Hitcount Desc"
lRet = objDB.FillRecordsetWeb(rsDetailedHits, sSQL)
can I use an else if statemnet within sSQL...where it says select top ..either top 10 or 25 or 50?
-
Send the SQL statement to the debug window and see what it looks like. Myabe the SQL statement needs some improvement.
-
Your SQL statement was incorrect:
I have rewritten it assuming all the field referenced are in a single table:
Code:
sSQL = "SELECT TOP " & intRowReqd & " DocumentName, HitCount " & _
"FROM RollupDocument " & _
"WHERE RollupType = " & iRollupType & " " & _
"AND RollupYear = " & iRollupYear & " " & _
"AND RollupValue = " & iRollupValue & " " & _
"ORDER BY Hitcount Desc"
The variable intRowReqd will be your selected 10, 25 or 50.
Your statement had two tables 'RollupDocument' and 'RollupLookupDocument' specified withot a JOIN. If the selected fields are from both then you should explicitly use the table.field in the SELECT and WHERE clause, using table1.joinfield = table2.joinfield' to JOIN them.
:cool:
