-
Mar 14th, 2015, 05:19 AM
#1
Thread Starter
Fanatic Member
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
-
Mar 14th, 2015, 05:44 AM
#2
Re: Sorting
Store your data as B01, B02 etc...
The sort is alpha, and B1, then B10, B11 is normal
-
Mar 14th, 2015, 06:27 AM
#3
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.
-
Mar 14th, 2015, 06:33 AM
#4
Re: Sorting
Originally Posted by fafalone
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.
-
Mar 14th, 2015, 06:38 AM
#5
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.
-
Mar 14th, 2015, 08:08 AM
#6
Re: Sorting
The use of "order by" in the OP is a pretty strong hint that he is talking about an sql query
-
Mar 14th, 2015, 08:22 AM
#7
Re: Sorting
Not a bad thing to remind people about though. Could come in handy for other things.
-
Mar 14th, 2015, 02:42 PM
#8
Re: Sorting
Originally Posted by Jagjit
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|