Re: 'ORDER BY' Issues

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Veign (NOSPAMinveign_at_veign.com)
Date: 09/03/04 

Date: Fri, 3 Sep 2004 00:15:46 -0400

Is the field truly alphanumeric as the sample data you show is all
numerical. If the field in the database is not a numerical type you can try
an on the fly conversion through SQL like:
"SELECT rNum FROM tblData ORDER BY CLng(rNum) DESC"

If you have an alphanumeric field post some of the sample data... 

-- 
Chris Hanscom
MVP (Visual Basic)
http://www.veign.com
--
"Joe" <Joe@discussions.microsoft.com> wrote in message
news:BD5C1C6A-279A-42C9-B3BB-58F193B45CAD@microsoft.com...
> I have an alphanumeric field I'm trying to sort by, and the query runs
> exactly the way it is supposed to.  But I was hoping there is a way to
change
> the way ADO sorts.
>
> Here is an example of a Query:
> "SELECT rNum FROM tblData ORDER BY rNum DESC"
> And here are the results:
> #7
> 116000
> 1222
> 1256
> 140
> 148999
> 178100
> 186028
> 19260
> 194176
>
> As you can see the data is sorted in the way ADO does it, but in an ideal
> sort it would look more like:
> #7
> 140
> 1222
> 1256
> 19260
> 116000
> 148999
> 178100
> 186028
> 194176
>
> I hope this makes sense to someone because I'm not sure how else to show
> what I 'm talking about.  I'm getting a lot of heat from superiors because
of
> this little issue and I've only been using ADO for about 6 months now.
>
> Thanks,
> Joe

Relevant Pages

  • Re: Exracting numerical data.
    ... Based on your sample data - if it is all in one column (but with rows ... irregularly between them) - then I would select the whole column and "Sort". ... I know there are available add-in user functions based on a loop ... The irregular spacing is for real. ...
    (microsoft.public.excel)
  • Re: Query sort not working after NULL column
    ... Provide some sample data where the sort isn't working right. ... Jerry Whittle, Microsoft Access MVP ... > Hope someone can shed light on this? ...
    (microsoft.public.access.queries)
  • Re: Sorting without grouping
    ... >sort on date. ... >totals are on Monthly Rate. ... Sample data below which I hope clarifies. ... I don't think you're missing something obvious. ...
    (microsoft.public.access.reports)
  • Re: Puzzle: Know Your Data
    ... I'm glad UV came up with hsolution, as mine was going to be similar, but involving the German double s that looks (sort of) like a Greek beta. ... strings, and you want to sort them but in a case-insensitive manner. ... # convert the strings to lowercase when comparing ... The puzzle to you is to determine the reason and sample data that ...
    (perl.beginners)
  • Re: rounding errors?
    ... >> numerical type far better suited to this sort of calculation that ... >> binary floating point. ... precision. ...
    (comp.lang.python)