Experts..need help!!

[pcabrol]

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?

[CCT]

Send the SQL statement to the debug window and see what it looks like. Myabe the SQL statement needs some improvement.

[Jerry Grant]

Your SQL statement was incorrect:
I have rewritten it assuming all the field referenced are in a single table:

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.