Controlling the sort order for records retrieved from an Access database

[Brit]

I have an ASP file that retrieves names from an Access database for 4 different categories of membership, which the visitor to the page selects (corporate, institutional, regular, or student). The DNS name is "cati", the names are specified in the "Last_names " field, and the categories are in the "categories " field.

l want the results sorted in alphabetic order by last name. However, the results appear to be in a totally random, non-alphabetic order, which is exactly the same every time I search. I do not understand what is causing them to be in this random order. Apart from that it works just fine.

Here is the code. The link to "identierV7.asp " simply displays more information when you click on one of the names in the search results. I would really appreciate any help!

<p><strong>List all CATI members by category:</strong></p>


<form BOTID="0" METHOD="POST" ACTION="category3.asp">

<select type="text" name=category size=4>
<option>Corporate</option>
<option>Institutional</option>
<option>Regular</option>
<option>Student</option>
</select>
<p>

  <input type="submit" value="Search">&nbsp;&nbsp;&nbsp;<input type="reset" value="Reset">

<font size="-1" color="#000000">

<!--webbot bot="SaveAsASP" CLIENTSIDE SuggestedExt="asp" PREVIEW=" " -->
  
</form>

<font face="Verdana, Arial, Helvetica, sans-serif" size="-1" color="#000000">

<p><strong>Note:</strong> When search results are displayed, click on a last name for more information about that translator or interpreter.
To return to the search results, use the <strong>Back</strong> button on your browser.</p>

<!--webbot bot="DatabaseRegionStart" startspan
s-columnnames="ID,Last_Name,First_Name,Title,Address,Extra_Address,City,State,Zip,Phone,Alternate_Email,Fax,Email,Local_Group,Category,Expires,Native_language,origin,Working_languages,Ata_certification,other_affiliations,Preferred_Fields,pager/cellphone,web_page,new,change_detail,change,codes,Biography"
s-columntypes="3,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,203,202,202,202,202,11,202,11,202,202,202"
s-dataconnection="cati" b-tableformat="FALSE" b-menuformat="FALSE"
s-menuchoice="Last_Name" s-menuvalue="Last_Name" b-tableborder="TRUE"
b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE"
b-listseparator="TRUE" i-ListFormat="0" b-makeform="TRUE"
s-recordsource="Members"
s-displaycolumns="ID,Last_Name,First_Name,Title,Address,Extra_Address,City,State,Zip,Phone,Alternate_Email,Fax,Email,Local_Group,Category,Expires,Native_language,origin,Working_languages,Ata_certification,other_affiliations,Preferred_Fields,pager/cellphone,web_page,Biography"
s-criteria="[category] CNT {category} +" s-order="[Last_name]"
s-sql="SELECT * FROM Members WHERE (category LIKE '%::category::%')"
b-procedure="FALSE" clientside SuggestedExt="asp"
s-DefaultFields="category=x" s-NoRecordsFound=""
i-MaxRecords="256" i-GroupSize="15" BOTID="0" u-dblib="_fpclass/fpdblib.inc"
u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="BODY"
local_preview="&lt;table border=0 width=&quot;100%&quot;&gt;&lt;tr&gt;&lt;td bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;Database Results regions will not preview unless this page is fetched from a Web server using a web browser. The section of the page from here to the end of the Database Results region will repeat once for each record returned by the query.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;&lt;/table&gt;"
preview="&lt;table border=0 width=&quot;100%&quot;&gt;&lt;tr&gt;&lt;td bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the start of a Database Results region.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;&lt;/table&gt;"
b-WasTableFormat="FALSE" --><!--#include file="_fpclass/fpdblib.inc"-->
<%
fp_sQry="SELECT * FROM Members WHERE (category LIKE '%::category::%')"
fp_sDefault="category=x"
fp_sNoRecords=""
fp_sDataConn="cati"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=15
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice="Last_Name"
fp_sMenuValue="Last_Name"
fp_iDisplayCols=20
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" i-CheckSum="5319" endspan -->

<!--------------------------- identifier ------------------------------------>

<p><a href="identifierV7.asp?ID=<!--webbot bot="DatabaseResultColumn" startspan
s-columnnames="ID,Last_Name,First_Name,Title,Address,Extra_Address,City,State,Zip,Phone,Alternate_Email,Fax,Email,Local_Group,Category,Expires,Native_language,origin,Working_languages,Ata_certification,other_affiliations,Preferred_Fields,pager/cellphone,web_page,new,change_detail,change,codes,Biography"
s-column="ID" b-tableformat="FALSE" b-hasHTML="FALSE" clientside
local_preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;ID&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;"
preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;ID&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" --><%=FP_FieldVal(fp_rs,"ID")%>

<!--webbot bot="DatabaseResultColumn" i-CheckSum="17991" endspan -->">

<!--------------------------- last name ------------------------------------>

<font face="Verdana, Arial, Helvetica, sans-serif" size="-1" color="#000099">

<p><strong><!--webbot bot="DatabaseResultColumn" startspan
s-columnnames="ID,Last_Name,First_Name,Title,Address,Extra_Address,City,State,Zip,Phone,Alternate_Email,Fax,Email,Local_Group,Category,Expires,Native_language,origin,Working_languages,Ata_certification,other_affiliations,Preferred_Fields,pager/cellphone,web_page,new,change_detail,change,codes,Biography"

s-column="Last_Name" b-tableformat="FALSE" b-hasHTML="FALSE" clientside
local_preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;Last_Name&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;"
preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;Last_Name&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" --><%=FP_FieldVal(fp_rs,"Last_Name")%></a></strong>,

<!--webbot bot="DatabaseResultColumn" i-CheckSum="17991" endspan -->

<!-------------------------- first name ------------------------------>

<strong><!--webbot bot="DatabaseResultColumn" startspan
s-columnnames="ID,Last_Name,First_Name,Title,Address,Extra_Address,City,State,Zip,Phone,Alternate_Email,Fax,Email,Local_Group,Category,Expires,Native_language,origin,Working_languages,Ata_certification,other_affiliations,Preferred_Fields,pager/cellphone,web_page,new,change_detail,change,codes,Biography"

s-column="First_Name" b-tableformat="FALSE" b-hasHTML="FALSE" clientside
local_preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;First_Name&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;"
preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;First_Name&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" --><%=FP_FieldVal(fp_rs,"First_Name")%></strong></font>

<!--webbot bot="DatabaseResultColumn" i-CheckSum="27645" endspan -->

<!--webbot bot="DatabaseRegionEnd" startspan b-tableformat="FALSE"
b-menuformat="FALSE" u-dbrgn2="_fpclass/fpdbrgn2.inc" i-groupsize="15" clientside
tag="BODY"
local_preview="&lt;table border=0 width=&quot;100%&quot;&gt;&lt;tr&gt;&lt;td bgcolor=&quot;#FFFF00&quot; align=&quot;center&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;End of Database Results region.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;&lt;TR&gt;&lt;TD ALIGN=LEFT VALIGN=MIDDLE&gt;&lt;FORM&gt;&lt;NOBR&gt;&lt;INPUT TYPE=Button VALUE=&quot;  |&lt;  &quot;&gt;&lt;INPUT TYPE=Button VALUE=&quot;   &lt;  &quot;&gt;&lt;INPUT TYPE=Button VALUE=&quot;  &gt;   &quot;&gt;&lt;INPUT TYPE=Button VALUE=&quot;  &gt;|  &quot;&gt;  [1/5]&lt;/NOBR&gt;&lt;/FORM&gt;&lt;/td&gt;&lt;/tr&gt;&lt;/table&gt;"
preview="&lt;table border=0 width=&quot;100%&quot;&gt;&lt;tr&gt;&lt;td bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the end of a Database Results region.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;&lt;TR&gt;&lt;TD ALIGN=LEFT VALIGN=MIDDLE&gt;&lt;NOBR&gt;&lt;INPUT TYPE=Button VALUE=&quot;  |&lt;  &quot;&gt;&lt;INPUT TYPE=Button VALUE=&quot;   &lt;  &quot;&gt;&lt;INPUT TYPE=Button VALUE=&quot;  &gt;   &quot;&gt;&lt;INPUT TYPE=Button VALUE=&quot;  &gt;|  &quot;&gt;  [1/5]&lt;/NOBR&gt;&lt;BR&gt;&lt;/td&gt;&lt;/tr&gt;&lt;/table&gt;" --><!--#include file="_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" i-CheckSum="62730" endspan -->

[DrBunchman]

Hi Brit, for some reason your post isn't appearing on the forum clearly but in general when you are retrieving data from a database you can add an ORDER BY clause to your sql string. Like this:

SELECT FirstName, LastName FROM Table1 ORDER BY LastName ASC

Where ASC stands for ascending (you could use DESC for descending).

Hope this helps,

Dr B