473,888 Members | 2,213 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Controlling the sort order for records retrieved from an Access database

1 New Member
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!

Expand|Select|Wrap|Line Numbers
  1. <p><strong>List all CATI members by category:</strong></p>
  4. <form BOTID="0" METHOD="POST" ACTION="category3.asp">
  6. <select type="text" name=category size=4>
  7. <option>Corporate</option>
  8. <option>Institutional</option>
  9. <option>Regular</option>
  10. <option>Student</option>
  11. </select>
  12. <p>
  14.   <input type="submit" value="Search">&nbsp;&nbsp;&nbsp;<input type="reset" value="Reset">
  16. <font size="-1" color="#000000">
  18. <!--webbot bot="SaveAsASP" CLIENTSIDE SuggestedExt="asp" PREVIEW=" " -->
  20. </form>
  22. <font face="Verdana, Arial, Helvetica, sans-serif" size="-1" color="#000000">
  24. <p><strong>Note:</strong> When search results are displayed, click on a last name for more information about that translator or interpreter.
  25. To return to the search results, use the <strong>Back</strong> button on your browser.</p>
  27. <!--webbot bot="DatabaseRegionStart" startspan
  28. 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"
  29. 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"
  30. s-dataconnection="cati" b-tableformat="FALSE" b-menuformat="FALSE"
  31. s-menuchoice="Last_Name" s-menuvalue="Last_Name" b-tableborder="TRUE"
  32. b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE"
  33. b-listseparator="TRUE" i-ListFormat="0" b-makeform="TRUE"
  34. s-recordsource="Members"
  35. 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"
  36. s-criteria="[category] CNT {category} +" s-order="[Last_name]"
  37. s-sql="SELECT * FROM Members WHERE (category LIKE '%::category::%')"
  38. b-procedure="FALSE" clientside SuggestedExt="asp"
  39. s-DefaultFields="category=x" s-NoRecordsFound=""
  40. i-MaxRecords="256" i-GroupSize="15" BOTID="0" u-dblib="_fpclass/fpdblib.inc"
  41. u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="BODY"
  42. 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;"
  43. 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;"
  44. b-WasTableFormat="FALSE" --><!--#include file="_fpclass/fpdblib.inc"-->
  45. <%
  46. fp_sQry="SELECT * FROM Members WHERE (category LIKE '%::category::%')"
  47. fp_sDefault="category=x"
  48. fp_sNoRecords=""
  49. fp_sDataConn="cati"
  50. fp_iMaxRecords=256
  51. fp_iCommandType=1
  52. fp_iPageSize=15
  53. fp_fTableFormat=False
  54. fp_fMenuFormat=False
  55. fp_sMenuChoice="Last_Name"
  56. fp_sMenuValue="Last_Name"
  57. fp_iDisplayCols=20
  58. fp_fCustomQuery=False
  59. BOTID=0
  60. fp_iRegion=BOTID
  61. %>
  62. <!--#include file="_fpclass/fpdbrgn1.inc"-->
  63. <!--webbot bot="DatabaseRegionStart" i-CheckSum="5319" endspan -->
  65. <!--------------------------- identifier ------------------------------------>
  67. <p><a href="identifierV7.asp?ID=<!--webbot bot="DatabaseResultColumn" startspan
  68. 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"
  69. s-column="ID" b-tableformat="FALSE" b-hasHTML="FALSE" clientside
  70. 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;"
  71. 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")%>
  73. <!--webbot bot="DatabaseResultColumn" i-CheckSum="17991" endspan -->">
  75. <!--------------------------- last name ------------------------------------>
  77. <font face="Verdana, Arial, Helvetica, sans-serif" size="-1" color="#000099">
  79. <p><strong><!--webbot bot="DatabaseResultColumn" startspan
  80. 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"
  82. s-column="Last_Name" b-tableformat="FALSE" b-hasHTML="FALSE" clientside
  83. 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;"
  84. 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>,
  86. <!--webbot bot="DatabaseResultColumn" i-CheckSum="17991" endspan -->
  88. <!-------------------------- first name ------------------------------>
  90. <strong><!--webbot bot="DatabaseResultColumn" startspan
  91. 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"
  93. s-column="First_Name" b-tableformat="FALSE" b-hasHTML="FALSE" clientside
  94. 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;"
  95. 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>
  97. <!--webbot bot="DatabaseResultColumn" i-CheckSum="27645" endspan -->
  99. <!--webbot bot="DatabaseRegionEnd" startspan b-tableformat="FALSE"
  100. b-menuformat="FALSE" u-dbrgn2="_fpclass/fpdbrgn2.inc" i-groupsize="15" clientside
  101. tag="BODY"
  102. 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;"
  103. 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"-->
  104. <!--webbot bot="DatabaseRegionEnd" i-CheckSum="62730" endspan -->
Mar 25 '08 #1
1 2366
979 Recognized Expert Contributor
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:

Expand|Select|Wrap|Line Numbers
  1. 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
Mar 26 '08 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Matt | last post by:
Hi all, We recently upsized two Microsoft Access Databases to SQL. We're using an ADP (2002) as the front end. All the conversion issues have been resolved, except for one: Whenever we insert a record into a table, the table isn't sorted by primary key like I would expect. Instead, the record can be found at the end of the table. This makes finding a particular record (especially as time goes on) very difficult.
by: tom r. | last post by:
I have a table with records in a desired sort order (I appended them to the table in the order I wanted). It would look something like this: Table 1 Name Date Code DisplayOrder ------ ------------ ----------- ------------------ A1 1-21-04 XB56 10 A1 1-21-04 XB56 20 A1 1-21-04 XB56 30
by: Damon Grieves | last post by:
Hi I just want to be sure I understand how the Access client works. If I have an Access back end with a million records on a server and an Access client. If the client is installed on the users pc and run by the user...does Access drag the whole million records across the LAN when we call up one record ie filters for that record locally? If the client is on the same server as the back end, and the user starts up the client on the server...
by: Darrel | last post by:
I'm creating a table that contains multiple records pulled out of the database. I'm building the table myself and passing it to the page since the table needs to be fairly customized (ie, a datagrid isn't going to work). On this page, people can update a variet of records. On submit, I want to then go in and update all of the records. Normally, I'd make each form element include a runat: server and then declare it in my codebhind so I...
by: mcollier | last post by:
I have some VB6 code that calls a SELECT query in Microsoft Access. When I run the query from access, the results appear in not particular order. However, when I call the query from VB6, via ADO, the rows are returned in order by one particular column/field. There is no ORDER BY on the query. For example, in Access, the rows may be returned as: RowID ------ 1
by: adrian.chandler | last post by:
Hi all, I have been using letter and symbol codes such as GNU< GNU\ GNU} GNUˆ in an Access table. I was surprised to see that when the table was sorted on this field, the order is: GNUˆ GNU_ GNU} GNU< I was expecting:
by: Les Juby | last post by:
I need to extract records from a database subject to conditions and only thereafter give the users the choice of which fields to sort the results on. In this situation I can't write back to a temporary file and then select all from that file on the different sort orderings. I figured that the only way out would be to write to an array from the first recordset set creation. Then write the report to the screen from the array and users...
by: xahlee | last post by:
Last year, i've posted a tutorial and commentary about Python and Perl's sort function. (http://xahlee.org/perl-python/sort_list.html) In that article, i discussed a technique known among juvenile Perlers as the Schwartzian Transform, which also manifests in Python as its “key” optional parameter. Here, i give a more detailed account on why and how of this construct. ----
by: muskie | last post by:
I've looked through as many posts about this as possible, but all end with no resolution. I simply need records from a table in random order, and I will be calling this recordset in a SQL statement from ASP. I've tried the following but it does not produce random order: SELECT * FROM Table1 ORDER BY Rnd(TableID) ASC; where TableID is an autonumber field. I've seen references to Randomize, but how do you use Access's
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.