Strange Sorting of Data

[KornGeek]

I have some data that I'm working with, and when I sort it, it isn't coming out in the order I would expect. (I'm using Access 2000 by the way.)

Most of records are sorted correctly, but there are a few exceptions. For instance, three of the key fields in the order they sorted are:
E-1006
E1030
E-1031

I expected that the E1030 record would have sorted far lower in the data. Access seems to be ignoring the "-" character during sorting. Unfortunately, I'm doing the sorting to prepare the data to be exported to another program that does a binary search on this field. This is messing it up.

Can anybody offer any insight as to why it's doing this and how to fix it?

 

[shannonp]

hmmmm strange one....maybe you could do a search and replace, replacing the "-" character with say an "x" providing you don't have any other values containing an 'x' value within the same field. Then simply sort again and cross (no pun intended) your fingers.

 

[KornGeek]

Unfortunately, I can't do that in this circumstance. This is the data that a customer will be entering into the database. If I change the format, it won't be found when they re-enter the data in the other part of the app. Also, I don't think my customer would be happy with the idea of me changing their format.

Thank you for the suggestion, but I need to figure out how to do this with the data I've been given.

 

[Steve101]

I've seen this sort of problem before when you try to sort memo type fields. If the field you're sorting is defined as a memo field, then either redefine it as a fixed length text field (if this is sensible), or alter the query to convert it into a text string by changing its reference to something like LEFT(YourMemoField, 100). This converts the memo field to a fixed length text field, and should fix the sorting for the purposes of the query.

Incidentally, if this is the cause of the problem AND you are attempting to sort by the memo field, one might question the design of the field type.

Hope this helps,
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[KornGeek]

Steve,
Thank you for your input. The field is a 15-character text field defined as the primary key of the table. It has the Required and Allow Zero Length properties set to No, Indexed set to Yes (No Duplicates), and Unicode Compression set to Yes. The Lookup tab for the field is left at the default Text Box setting. This table has no relationships to any other tables.

I can't think of any other info to give you about this table. I believe that this is the way Access sorts text by default, and I'm trying to figure out a way around it.

Thank you.

 

[WildHare]

This is definitely an odd one. I just tried to duplicate this, and used values like E10, E-100, and E9, and they sorted in that order..

E10
E-100
E9

Somehow, somethin' just don't look right..Anybody wanna call M/S about this?...

Kornie, all I can do is reassure you that it's (Probably) not YOU... LOL

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[Steve101]

Yes, most curious indeed. One way round it is to use a custom written function to temporarily replace the dash in the query with another character (eg. an underscore). The following is an example of such a function:

Function substDash(YourField)
NewString = ""
ReplacementChar = "_"
YourField = Nz(YourField, "&quot
For i = 1 To Len(YourField)
ThisChar = Mid(YourField, i, 1)
If ThisChar = "-" Then
NewString = NewString & ReplacementChar
Else
NewString = NewString & ThisChar
End If
Next i
substDash = NewString
End Function

Then in your SQL, instead of your "ORDER BY YourField", you could use "ORDER BY substDash(YourField)", which would effect a replacement of the offending dash character with another character (in this case an underscore); this replacement would only affect the sortorder and NOT the actual data.

Hope this helps,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)