
Reformatting and Sorting
I'm have a twotiered problem. I've been told that there is a way to do this, but I can't seem to locate it.<BR><BR>My situation is this: I have a View from an SQL database that contains 6 fields. I am creating an .asp page where the contents of these 6 fields are displayed, and the column heads are links that allows the user to sort by any one of the 6 fields. All the data that I am receiving are numbers formatted as Text, and consequently end up with sorts that are alphabetical, but not numerical. Like so:<BR><BR>90<BR>9<BR>85<BR>8<BR>7<BR>56<BR>52<BR> 52 <BR>5<BR>49<BR>48<BR>4<BR>389<BR>37<BR>3<BR><BR>An d so forth.<BR><BR><BR>I have no write access to the database. Since all the data is numeric it would be easiest to change the data type in the database, but this is not an option for me.<BR><BR><BR>What I need to do is find some way to sort these numbers formatted as text so that they make sense, numerically. I also need to be able to pull the largest number from each field.<BR><BR>Whether this is through creating a virtual recordset where I can define the parameters including the datatype, or if there is just some simple, easy way to read it from the view and onto the page as numeric...any and all help or pointers to something that might work would be greatly appreciated. Thanks!<BR>

RE: Reformatting and Sorting
oh god... you have written the code already!!!

RE: Reformatting and Sorting
*blink* *blink*<BR><BR>Yes, I have already written the code which connects to the database, retrieves the view, and puts it on the page. It's easy enough to modify, though...I'm not sure I understand you.

This should work..
Why not covert the output into an array then use a sort array function on it?<BR><BR>col is the column that the numbers(text) appear in the array<BR><BR>SQL = "SELECT x,y,z,number(text) field FROM ....."<BR>Set RS = conn.Execute( SQL ) <BR>Dim aTable1Values<BR>aTable1Values = RS.GetRows()<BR>ArraySort aTable1Values, 3<BR><BR>I've chosen 3 because this is the 4th field I've selected in the SQL statement and arrays go from 0 upwards in column rather than 1 upwards.<BR><BR><%function arraysort(values(),col)<BR> Dim i <BR> Dim j <BR> Dim smallest_value <BR> Dim smallest_j <BR> Dim min<BR> Dim max<BR> Dim temp<BR> Dim intA<BR> <BR> min = lbound(values,2)<BR> max = ubound(values,2)<BR> For i = min To max  1<BR> smallest_value = values(col,i)<BR> smallest_j = i<BR> For j = i + 1 To max<BR> ' See if values(j) is smaller. changed To strComp to work With strings.<BR> 'number sort ASEC<BR> if values(col,j)<smallest_value Then<BR> ' Save the new smallest value.<BR> smallest_value = values(col,j)<BR> smallest_j = j<BR> End if<BR> Next 'j<BR> <BR> 'text sort<BR> 'if strComp(values(col,j),smallest_value,vbTextCompare ) = 1 Then<BR> <BR> if smallest_j <> i Then<BR> ' Swap items i and smallest_j.<BR> For intA = 0 To ubound(values,1)<BR> temp = values(intA,smallest_j)<BR> values(intA,smallest_j) = values(intA,i)<BR> values(intA,i) = temp<BR> Next 'intA<BR> End if<BR> Next 'i<BR> arraysort = values<BR> End function<BR><BR>%><BR><BR>Mazza ;)
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

