Solved Funny sorting (1 Viewer)

[lodmark]

I am having trouble sorting a table.
When I choose to sort it alphabetically, it comes in two parts. First there are about 500 records in one part and then the remaining about 13,000 records come in a separate part, but in the same table.
The picture shows when the first part ends and the second part begins.

Any Thoughts?

Leif

 

[arnelgp]

on which field(s) did you sort?

 

[lodmark]

The second one.
Leif

 

[DickyP]

The example IS alphabetical (ANSI codes)

 

[arnelgp]

not sure if there are "non-printable" characters on your field.
sometimes these hidden chars are the culprit.
they can get there by copy/pasting from other source apps.

 

[arnelgp]

The example IS alphabetical (ANSI codes)

what do you mean by ANSI codes?

 

[isladogs]

What is your default sort order setting in Access Options?

 

[lodmark]

 

[lodmark]

not sure if there are "non-printable" characters on your field.
sometimes these hidden chars are the culprit.
they can get there by copy/pasting from other source apps.

I've tried to rewrite some of the posts whitout any luck.
Leif

 

[lodmark]

I Think I've solved it.
There was some spaces at the end of the post.
I will no try to rewrite the rest and se if it's solves everyone.

Thank's for the replies.

Leif

 

[lodmark]

A funny thing is that I can't just delete the spaces from behind.
Then the last carachter disapears. I'll have to copy the post to a text editor and from there copy and past it back.

Leif

 

[arnelgp]

you can create an Update query to remove those "space" thing (be sure to make a copy of your table first, for backup).

Update YourTableName Set [SecondFieldName] = RemoveNonPrintable([SecondFieldName]) Where Not ([SecondFieldName] Is Null);

on a module:

Function RemoveNonPrintable$(ByVal strInput$)
    Dim i&
    Dim ch$
    Dim result$

    For i = 1 To Len(strInput)
        ch = Mid$(strInput, i, 1)
        If Asc(ch) >= 32 Then
            result = result & ch
        End If
    Next i

    RemoveNonPrintable = Trim$(result)
End Function

 

[The_Doc_Man]

Since you are using extended character sets, what are your Unicode-related options and settings?

Because you have set a non-ASCII database sort order (you chose Swedish/Finnish), the internal DB sort order will not follow the strict binary value of the character codes. This "linguistic" sort order is shown as being followed with the four records preceding the highlighted first record, which starts with a parenthesis. What sort order did you expect/desire?

The problem here is knowing where special characters fall in a linguistic-based sort order. Can we assume that the 13,000 records in your second sort group all begin with punctuation? Are there any records in that second group that begin with punctuation other than a parenthesis? While I understand your concern, my question is, are the 13,000 records of the second sub-group sorted correctly if you ignore the records that DON'T start with punctuation? It is possible that this is an artifact of the linguistic sort algorithm as applied to special characters.

 

[arnelgp]

Because you have set a non-ASCII database sort order (you chose Swedish/Finnish), the internal DB sort order will not follow the strict binary value of the character codes. This "linguistic" sort order is shown as being followed with the four records preceding the highlighted first record, which starts with a parenthesis. What sort order did you expect/desire?

i tested the finnish sort order on a test db with some records with extended chars and it sorted right, same as in english sort order.

 

[lodmark]

It was not a space but a TAB character in front of the text in the posts.
So it's solved.
Thanks for the code arnelgp!

Leif

 

[isladogs]

Re your screenshot in post #8, although unrelated to your (solved) sort problem, I would strongly recommend that you do not work with databases in any online storage such as OneDrive as it significantly increases the chances of corruption.

OneDrive is fine for archiving databases but not as the default location

 

[lodmark]

Re your screenshot in post #8, although unrelated to your (solved) sort problem, I would strongly recommend that you do not work with databases in any online storage such as OneDrive as it significantly increases the chances of corruption.

OneDrive is fine for archiving databases but not as the default location

I'm totally with you there @isladogs . As you already understood it's only the image that's on OneDrive.

Have a nice day.
Leif

 

[isladogs]