Annoying String Sorting Problem

[David M C]

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

 

[Rick Brandt]

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))

 

[BruceM]

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]

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

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.

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

 

[Matthias Klaey]

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

 

[David M C]

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]

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 explore:

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