My asp page has buttons that sort (via URL params) the Access DB results by column - using order by ASC or DESC. I have a column that keeps track of percentages, where they exist, and other txt such as Withdrawn, etc where these percentages are/will not be available. The field can also be blank, awaiting the percentages. I'm having trouble sorting/ordering by this column.
I would like the column to be sorted numerically with the highest percentage (number) showing up as the first record. What's happening now is the first rows being displayed are Withdrawn (quite a few pages), followed alphabetically by other "other" text, then by the numbers (the percentages), when ordered by DESC. Even so, the # 5 is sorted in the same cat as 55. When ordered by ASC, the empty fields show up first, followed by the #s, which as mentioned above are in the wrong order - 9 gets displayed before 72.
Can order by be made to 'think' smarter, or is there a way I can check whether the contents of the field are a # and then display them by the highest to smallest #, followed by other categories, and then the blank rows? Order by, by itself doesn't seem to handle this very well!!!
I also read that one can use the CDbl conversion funciton to convert the results of a field to a double (the type that the %s should be interpreted as) but I'm not sure how to use this in my code. Any ideas on this, or other alternatives?
HERE's my code:
The asp page contains URL params (sortby & orderby) which are appended to the main SQL query that retrieves info from the DB. Here's the query:
rsProposals.Source = "SELECT * FROM [My Table] ORDER BY " + Replace(rsProposals__sortb
y, "'", "''") + " " + Replace(rsProposals__order
by, "'", "''") + ";"
where:
rsProposals is the recordset,
Dim rsProposals__sortby
rsProposals__sortby = "company"
If (request.querystring("sort
by") <> "") Then
rsProposals__sortby = request.querystring("sortb
y")
End If
So if, I click on the link that has the sortby param as the column name [Vote Result] and orderby param as DESC, I would like the results to be sorted accordingly, which, as explained above, is not happening.
The DB results are spewed via a while loop that looks something like:
<% While ((Repeat1__numRows <> 0) AND (NOT rsProposals.EOF)) %>
<td><font size="-1" face="Arial, Helvetica, sans-serif"><%=(rsProposal
s.Fields.I
tem("Compa
ny").Value
)%></font>
</td>
<td><font size="-1" face="Arial, Helvetica, sans-serif"><%=(rsProposal
s.Fields.I
tem("Meeti
ng").Value
)%></font>
</td>
<td><font size="-1" face="Arial, Helvetica, sans-serif"><%=(rsProposal
s.Fields.I
tem("Vote Result").Value)%></font></
td>
</tr>
<%
Repeat1__index=Repeat1__in
dex+1
Repeat1__numRows=Repeat1__
numRows-1
rsProposals.MoveNext()
Wend
%>
HTH
