If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Annoying String Sorting Problem
It seems Access isn't so clever at sorting strings. Each different type of
string requires a different sorting method. For instance, given a field, LocationName: Bathroom Bedroom Kitchen Lounge Requires a simple sort on LocationName. 1 The Green 2 The Green 3 The Green 10 The Green Requires a sort on Val(LocationName). Room 1 Room 2 Room 3 Room 10 Requires a sort I can't figure out. How do you sort those strings? Secondly, is there a way to sort all the above with one solution? Thanks Dave |
#2
|
|||
|
|||
Annoying String Sorting Problem
David M C wrote:
It seems Access isn't so clever at sorting strings. Each different type of string requires a different sorting method. For instance, given a field, LocationName: Bathroom Bedroom Kitchen Lounge Requires a simple sort on LocationName. 1 The Green 2 The Green 3 The Green 10 The Green Requires a sort on Val(LocationName). Room 1 Room 2 Room 3 Room 10 Requires a sort I can't figure out. How do you sort those strings? Secondly, is there a way to sort all the above with one solution? Thanks Dave If you sort a Text field in Access it is sorted alphabetically. If you sort a numeric field it is sorted numerically. If you sort a DateTime field it is sorted chronologically. If you have a Text field that happens to contain numbers and you want a numerical sort then you have to use an expression that converts the digit-strings into actual numeric values and then sort on the expression. I fail to see what could be more "clever" about how sorting in Access (and every other database) could work. If you want a solution that will magically detect any time there are digits in your text field so that a numeric sort can be applied then no, there is not one. You could write a custom function that would do this, but then how would it handle a value like "aaa12nnn45"? Should it sort that numerically on the 12, the 45, or on 1245? In your specific example if the text before the digits is always "Room " then you can use... SortVal: Val(Mid([FieldName], 6)) -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#3
|
|||
|
|||
Annoying String Sorting Problem
I expect your examples are simplifications of the real-life situation, but
in general you could look for ways to store just the number. For instance, if you are storing room numbers, there may be no need to add "Room" to every field. However, if you wish to sort the Room entries as you have presented them you could use something like: Val(Right([RoomField],Len([RoomField]) - 5)) I agree with Rick that there is no way you can expect Access to interpret your sorting needs. There are just too many options for a field containing a number. "David M C" wrote in message ... It seems Access isn't so clever at sorting strings. Each different type of string requires a different sorting method. For instance, given a field, LocationName: Bathroom Bedroom Kitchen Lounge Requires a simple sort on LocationName. 1 The Green 2 The Green 3 The Green 10 The Green Requires a sort on Val(LocationName). Room 1 Room 2 Room 3 Room 10 Requires a sort I can't figure out. How do you sort those strings? Secondly, is there a way to sort all the above with one solution? Thanks Dave |
#4
|
|||
|
|||
Annoying String Sorting Problem
I'll obviously have to write my own sorting function. However, sorting
strings is something Windows seems to do very well. Just look at how your files and folders are sorted by name in Windows Explorer. Thanks, Dave "BruceM" wrote: I expect your examples are simplifications of the real-life situation, but in general you could look for ways to store just the number. For instance, if you are storing room numbers, there may be no need to add "Room" to every field. However, if you wish to sort the Room entries as you have presented them you could use something like: Val(Right([RoomField],Len([RoomField]) - 5)) I agree with Rick that there is no way you can expect Access to interpret your sorting needs. There are just too many options for a field containing a number. "David M C" wrote in message ... It seems Access isn't so clever at sorting strings. Each different type of string requires a different sorting method. For instance, given a field, LocationName: Bathroom Bedroom Kitchen Lounge Requires a simple sort on LocationName. 1 The Green 2 The Green 3 The Green 10 The Green Requires a sort on Val(LocationName). Room 1 Room 2 Room 3 Room 10 Requires a sort I can't figure out. How do you sort those strings? Secondly, is there a way to sort all the above with one solution? Thanks Dave |
#5
|
|||
|
|||
Annoying String Sorting Problem
David M C wrote:
I'll obviously have to write my own sorting function. However, sorting strings is something Windows seems to do very well. Just look at how your files and folders are sorted by name in Windows Explorer. Thanks, Dave The proper solution to your problem is to introduce a new numeric (Integer) field in your table, say, "SortOrder", and the you fill in the "correct" sort order according to your definition into this field, and then use the field SortOrder to order your text field. The deeper reason is that the example values that you present are basically arbitrary and would need a semantical context to understand what kind of order *may* be present in these values. And this ist way beyond what computers can do today (and tommorow, ald probably at least for the next 20 years). HTH Matthias Kläy -- www.kcc.ch |
#6
|
|||
|
|||
Annoying String Sorting Problem
I don't believe it is way beyond what computers can do today (in fact, I know
this is not the case). Just look at the way Windows organises files and folders for a logical way of sorting strings with numerical parts. It will quite happily sort the following strings into order: 1 Doc 2 Doc 3 Doc 10 Doc And these: Doc 1 Doc 2 Doc 3 Doc 10 And these: Doc 1 Doc Doc 2 Doc Doc 3 Doc Doc 10 Doc And these: Doc 1 FFF Doc 2 BBB Doc 3 HHH Doc 10 AAA Now I just have to work out how to get Access to do the same. "Matthias Klaey" wrote: David M C wrote: I'll obviously have to write my own sorting function. However, sorting strings is something Windows seems to do very well. Just look at how your files and folders are sorted by name in Windows Explorer. Thanks, Dave The proper solution to your problem is to introduce a new numeric (Integer) field in your table, say, "SortOrder", and the you fill in the "correct" sort order according to your definition into this field, and then use the field SortOrder to order your text field. The deeper reason is that the example values that you present are basically arbitrary and would need a semantical context to understand what kind of order *may* be present in these values. And this ist way beyond what computers can do today (and tommorow, ald probably at least for the next 20 years). HTH Matthias Kläy -- www.kcc.ch |
#7
|
|||
|
|||
Annoying String Sorting Problem
David M C wrote:
I don't believe it is way beyond what computers can do today (in fact, I know this is not the case). Just look at the way Windows organises files and folders for a logical way of sorting strings with numerical parts. It will quite happily sort the following strings into order: 1 Doc 2 Doc 3 Doc 10 Doc And these: Doc 1 Doc 2 Doc 3 Doc 10 And these: Doc 1 Doc Doc 2 Doc Doc 3 Doc Doc 10 Doc And these: Doc 1 FFF Doc 2 BBB Doc 3 HHH Doc 10 AAA Now I just have to work out how to get Access to do the same. Yes, but it also sorts Room 2 Room 1 Are you willing to accept this? I don't know what algorithm the Windows file sort uses, but here is an idea that you might explo Split the strings into numbers only and the nonnumber parts, put the parts each in its own column and do a multiple sort on theses columns from left to right. This should get you pretty close to what you want. Greetings Matthias Kläy -- www.kcc.ch |
Thread Tools | |
Display Modes | |
|
|