I am having a very strange problem when I try to group the results of my 

Recordset.



I would like to display the category heading followed by the articles 

themselves  in a table, and when the recordset hits a new category , to 

generate  the new category heading. I have used this logic on a display 

page of all of my DB but this one is based on search criteria.



Basically I am finding that the category is getting listed AFTER the 

articles are- even though it is written to display the category heading 

first. I have tried this logic on a another page (as I have stated) and 

I didn't run into this problem.

Any help would be appreciated.



CODE/



' FUNCTION TO FX THE APOSTROPHE PROBLEM

FUNCTION theFIX(variable)

theFIX=3DReplace(variable, "'", "''")

END FUNCTION





Response.Buffer = True



'check for errors

On Error Resume Next







Dim Cmd

Dim srchval 

Dim RS

Dim SQL

Dim varsearch

Dim currentcat

srchval=Request.Querystring("searchvalue")

varsearch= Request.Querystring("action")

'response.write srchval &"<br>"



'DSN-less connection

DataDir = _

  Server.MapPath("..\data\data.mdb")

Set Cmd = Server.CreateObject("ADODB.Command")

Cmd.ActiveConnection = _

  "Driver=" & _

  "{Microsoft Access Driver (*.mdb)};" & _

  "DBQ=" & DataDir



SQL = "SELECT id, title, author, artdate, category FROM articles 

WHERE"

if varsearch="text" then

SQL=SQL & " article LIKE  '%"& theFIX(srchval) &"%'"

end if

if varsearch="author" then

SQL=SQL & " author LIKE '%"& theFIX(srchval) &"%'"

end if



if varsearch="datesrch" then

SQL=SQL & " artdate BETWEEN #"& 

Request.Querystring("searchvaluestart") &"# AND #"& 

Request.querystring("searchvalueend") &"#"

end if



SQL=SQL &" GROUP BY category, author, title, id, artdate ORDER BY 

category DESC"





 

  Cmd.CommandText = SQL

Set RS = Cmd.Execute

RS.movefirst

currentcat = RS("category")







If RS.BOF And RS.EOF Then

Response.Write "Your search for <font size='+1'>"& srchval &"</font> 

did not return any matching records."

'otherwise...

Else

'build the HTML table to display the records.





response.write"Your search for "& Chr(34) & srchval & Chr(34) &" 

retrieved the following articles.<br>"

Response.Write "<center><table border=1>"

'write the header of the first category



response.write"<tr><td bgcolor=3D'#666666'><p class='title'>"& 

Ucase(currentcat) &"</td></tr>"



Do While NOT RS.EOF

'now if the current category is different- make a new header row.



if RS("category") <> currentcat then

response.write"<tr><td bgcolor='#666666'><p class='title'>"& 

Ucase(currentcat) &"</td></tr>"

currentcat=RS("category")

end if

response.write"<tr><td><p class=3D'content'><b><a 

href='text_file.asp?pick="& RS("id") &"'>"& RS("title") &"</a></b> - 

"& RS("artdate") &"<br>"& RS("author") &"</td></tr>"

RS.movenext

loop







response.write"</table></center>"

end if



'confirm error reports

If Err.Number = 0 Then

'response.write"<br>Search Should have been successful"

Else

response.write"<br>The search contained errors"

end if



'close and destroy your connection and recordset objects

RS.Close



Set RS = Nothing







TIA



SPENCE