Results 1 to 8 of 8

Thread: Sorting

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Posts
    688

    Sorting

    Hi

    I have Text field . Data is stored like this.

    B1,B2,B3,B4,B5,B6,B7,B8,B9,B10,B11,B12,B20 . When i gave Order By then it shows like below

    B1,B10,B11,B12,B2,B20 etc

    But i wanted it should display as above
    B1,B2,B3,B4,B5,B6,B7,B8,B9,B10,B11,B12,B20

  2. #2
    Fanatic Member
    Join Date
    Jan 2015
    Posts
    596

    Re: Sorting

    Store your data as B01, B02 etc...

    The sort is alpha, and B1, then B10, B11 is normal

  3. #3
    PowerPoster
    Join Date
    Jul 2010
    Location
    NYC
    Posts
    5,653

    Re: Sorting

    You can use the StrCmpLogicalW API for whats called a natural sort. It's the same thing Explorer uses to sort file names. There's several examples if you search around. Here's one that uses it to add items into the correct place instead of trying to rearrange later: http://www.vbforums.com/showthread.p...=1#post4653221
    Last edited by fafalone; Mar 14th, 2015 at 06:34 AM.

  4. #4
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Sorting

    Quote Originally Posted by fafalone View Post
    You can use the StrCmpLogicalW API for whats called a natural sort. It's the same thing Explorer uses to sort file names. There's several examples if you search around.
    I doubt most DBMSs support API calls inline in SQL expressions.

    But you don't have to sort on raw data either. You could either sort on reformatted values, or better yet store reformatted values for sorting purposes so you can index them.

    Or even store reformatted and then reformat back to collapsed/abbreviated form on query to avoid duplication.

  5. #5
    PowerPoster
    Join Date
    Jul 2010
    Location
    NYC
    Posts
    5,653

    Re: Sorting

    It wasn't particularly clear where the data came from or that it was a data linked control rather than a regular textbox with data just in it.

  6. #6
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Sorting

    The use of "order by" in the OP is a pretty strong hint that he is talking about an sql query

  7. #7
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Sorting

    Not a bad thing to remind people about though. Could come in handy for other things.

  8. #8
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Sorting

    Quote Originally Posted by Jagjit View Post
    Hi

    I have Text field . Data is stored like this.

    B1,B2,B3,B4,B5,B6,B7,B8,B9,B10,B11,B12,B20 . When i gave Order By then it shows like below

    B1,B10,B11,B12,B2,B20 etc

    But i wanted it should display as above
    B1,B2,B3,B4,B5,B6,B7,B8,B9,B10,B11,B12,B20
    In case it's:
    - JET-SQL (when you work against an *.mdb)
    and
    - your Text-DataField always starts with a single Alphabetic-Char, followed by numeric chars...

    ...then your Order By Clause could be written as:
    "... Order By Left(YourField, 1), Val(Mid(YourField, 2)) "

    If that's against a table which has tens of thousands of records, then you might
    want to look for a better solution - otherwise it worth's a try.

    Olaf

Posting Permissions

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



Click Here to Expand Forum to Full Width