order by clause in the sql statement

<i><b>Originally posted by : Tom Austen (tomausten@hotmail.com)</b></i><br />Hi,<br /><br />I'm using the following bit of code to generate the sql. The code only uses one submit button but one of five different inputs. <br /><br />If request ("Firstname") <> "" Then <br /> sSearch = sSearch & " Forename LIKE '" & Request("Firstname") & "%' ORDER BY Forename" <br />End If<br />If request ("Surname") <> "" Then <br /> sSearch = sSearch & " Surname LIKE '" & Request("Surname") & "%' ORDER BY Surname" <br />End If<br />If request ("Department") <> "" Then <br /> sSearch = sSearch & " Body.Body_Code='" & Request("Department") & "' ORDER BY Forename"<br />End If<br /><br />Set rst = Server.CreateObject("ADODB.Recordset")<br />Set con = Server.CreateObject("ADODB.Connection")<br /><br />con.Open Application("Connection")<br /><br />' Create the SQL statement and stick it in varSQL<br />SQL = "SELECT Person.Person_ID, Person.Surname, Person.Forename, Person.Office_Telephone_Number, Venue.Fax_Number, Body.Name_of_Body, Person.Job_Title, [Town_Name] & ' ' & [Building_Name] & IIf(IsNull([venue].[room_number]),'',' Room ' & [venue].[room_number]) & ' ' & [venue].[room_name] AS Expr1 FROM ([Town Table] INNER JOIN ([Building Table] INNER JOIN Venue ON [Building Table].Building_Code = Venue.Building_Code) ON [Town Table].Town_Code = [Building Table].Town_Code) INNER JOIN (Person INNER JOIN (Body INNER JOIN Membership ON Body.Body_Code = Membership.Body_code) ON Person.Person_ID = Membership.Person_ID) ON Venue.Venue_Code = Person.usual_venue_code WHERE (((Body.Type_of_Body_Code)='bu') AND ((Membership.Membership_Start_Date)<Date() Or (Membership.Membership_Start_Date) Is Null) AND ((Membership.Membership_End_Date)>Date() Or (Membership.Membership_End_Date) Is Null))"<br /><br />If request ("SectionDept") <> "" Then <br /> SQL = SQL & " ORDER BY Name_Of_Body"<br />End If<br />If request ("SectionSur") <> "" Then <br /> SQL = SQL & " ORDER BY Surname"<br />End If<br /><br />if sSearch <> "" Then <br /> SQL = SQL & " AND " & sSearch<br />End If<br /><br />I need to get the SectionDept request to add an ORDER BY on the end of the SQL statement that will order the results by name_of_body and Surname, at the moment the results are ordered by name_of_body only. <br /><br />How do I order them by both or is it not possible?<br /><br />Thanx<br />Tom<br />

<i><b>Originally posted by : </b></i><br /><br /><br />Change your code:<br />If request ("SectionDept") <> "" Then <br /> SQL = SQL & " ORDER BY Name_Of_Body"<br /><br />to<br />If request ("SectionDept") <> "" Then <br /> SQL = SQL & " ORDER BY Name_Of_Body, Surname "<br />------------<br />Tom Austen at 1/17/2001 5:20:44 AM<br /><br />Hi,<br /><br />I'm using the following bit of code to generate the sql. The code only uses one submit button but one of five different inputs. <br /><br />If request ("Firstname") <> "" Then <br /> sSearch = sSearch & " Forename LIKE '" & Request("Firstname") & "%' ORDER BY Forename" <br />End If<br />If request ("Surname") <> "" Then <br /> sSearch = sSearch & " Surname LIKE '" & Request("Surname") & "%' ORDER BY Surname" <br />End If<br />If request ("Department") <> "" Then <br /> sSearch = sSearch & " Body.Body_Code='" & Request("Department") & "' ORDER BY Forename"<br />End If<br /><br />Set rst = Server.CreateObject("ADODB.Recordset")<br />Set con = Server.CreateObject("ADODB.Connection")<br /><br />con.Open Application("Connection")<br /><br />' Create the SQL statement and stick it in varSQL<br />SQL = "SELECT Person.Person_ID, Person.Surname, Person.Forename, Person.Office_Telephone_Number, Venue.Fax_Number, Body.Name_of_Body, Person.Job_Title, [Town_Name] & ' ' & [Building_Name] & IIf(IsNull([venue].[room_number]),'',' Room ' & [venue].[room_number]) & ' ' & [venue].[room_name] AS Expr1 FROM ([Town Table] INNER JOIN ([Building Table] INNER JOIN Venue ON [Building Table].Building_Code = Venue.Building_Code) ON [Town Table].Town_Code = [Building Table].Town_Code) INNER JOIN (Person INNER JOIN (Body INNER JOIN Membership ON Body.Body_Code = Membership.Body_code) ON Person.Person_ID = Membership.Person_ID) ON Venue.Venue_Code = Person.usual_venue_code WHERE (((Body.Type_of_Body_Code)='bu') AND ((Membership.Membership_Start_Date)<Date() Or (Membership.Membership_Start_Date) Is Null) AND ((Membership.Membership_End_Date)>Date() Or (Membership.Membership_End_Date) Is Null))"<br /><br />If request ("SectionDept") <> "" Then <br /> SQL = SQL & " ORDER BY Name_Of_Body"<br />End If<br />If request ("SectionSur") <> "" Then <br /> SQL = SQL & " ORDER BY Surname"<br />End If<br /><br />if sSearch <> "" Then <br /> SQL = SQL & " AND " & sSearch<br />End If<br /><br />I need to get the SectionDept request to add an ORDER BY on the end of the SQL statement that will order the results by name_of_body and Surname, at the moment the results are ordered by name_of_body only. <br /><br />How do I order them by both or is it not possible?<br /><br />Thanx<br />Tom<br />