Sorting results

Results 1 to 2 of 2

Thread: Sorting results

  1. #1
    Join Date
    Dec 1969

    Default Sorting results

    Using Access DB<BR>How do you make this in query results:<BR><BR>1100 cc<BR>1200 cc<BR>20 cc<BR>30 cc<BR>40 cc<BR><BR>Do this in query results:<BR><BR>20 cc<BR>30 cc<BR>40 cc<BR>1100 cc<BR>1200 cc<BR><BR>Using ASC doesnt work.

  2. #2
    Join Date
    Dec 1969

    Default Not obvious...

    ...the problem is that you have to get Access to ignore the non-numeric part of the field and sort the numeric part as a number.<BR><BR>Whoever designed this DB should be shot, for putting what is essentially numeric data into a text field. The best thing you could do would be to redesign the table to make this a numeric field. After all, if the field name is "numberOfCCs" then why do you need the silly " cc" suffix?<BR><BR>Anyway...<BR><BR>SELECT * FROM table <BR>ORDER BY CDBL( Left( theField, InStr( theField, " " ) - 1 ) )<BR><BR>should do it. This *assumes* that every value in that field has a space in it! If there is any field missing the space, it will fail. That, too, could be provided for, but it would be much more complicated. <BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts