Alpha/numeria/punctuation sort order

[PatP]

I have a database that is not sorting as I feel it should. Can anyone tell me how Access prioritizes re. letters A-Z, upper-case, A-Z, lower case, numbers and punctuation marks

The field on which I am trying to sort is a text field

Would appreciate any help.

Many thanks.

 

[Simonc]

numbers sort
001
002
025
10
210
22
34
as if they are letters. Number will come before letters in a text field.
Symbols will usually come before numbers, and it should make little difference case-wise.

 

[PatP]

Thank you for the input. Based on that, does it make sense that Access would sort these items in this manner?

GC-100
GC104
GC-150
GC204

I just feel all the hyphenated values should be grouped, then those without. Can you explain the logic to me?

Many thanks.

PatP

 

[david epsom dot com dot au]

1) Your sort order depends on which sort order you select for the database.
You can select the sort order when you create a new database.

2) Are you sure those items are sorted? What makes you think they are
sorted?

3) Normal 'English' sort order is: (symbol,number,letter)

GC-100
GC-150
GC104
GC204

(david)

Thank you for the input. Based on that, does it make sense that Access

would sort these items in this manner?

GC-100
GC104
GC-150
GC204

I just feel all the hyphenated values should be grouped, then those

without. Can you explain the logic to me?

 

[PatP]

(1) The new database sort type is set to "general." Any of the other choices I have pertain to foreign languages

(2) I went into the table, selected the field, and then sorted on it. Any reports I print, using this field as the primary sort, also print out in the order I stated

(3) Your normal "English" sort order is what I expect

Any ideas

Pat

 

[david epsom dot com dot au]

What do you get if in the immediate window you type:

?currentdb.CollatingOrder

What do you get if you type:

?currentdb.TableDefs("MyTable").fields("FieldName").collatingorder

(You would expect 1033 )

(1) The new database sort type is set to "general." Any of the other

choices I have pertain to foreign languages.

(2) I went into the table, selected the field, and then sorted on it. Any

reports I print, using this field as the primary sort, also print out in the
order I stated.

 

[Pat P]

I really appreciate your feedback, and I hate to admit to being so dumb

Where do you type the info? The immediate window? Where exactly?

If you could draw me a picture, I would be very grateful

Pat

 

[david epsom dot com dot au]

To get the Immediate Window, open the database in Access,
then type Control G ( [ctrl][g] ).

Or open any code module, then select from the menu View,
Immediate Window.


Also, what are your general (Settings, Control Panel, Regional Options)
for Locale and Input Locale?
(david)

 

[PatP]

Thank you for the information! I've never done that before

Anyhow, both expressions return a result of 1033

When I go into Control Panel, I can find no direct reference to Locale and Input Locale, but everything says US English
Under the "Page Code Conversion Table" there are a lot of strange things checked, some of which are greyed out. I am running Windows XP

I guess I could just put a sort field in, use that as the primary sort, then sort by part number. That just seems the long way round the barn and should not be necessary

I really appreciate your help. If you can think of anything else, I'll keep checking

Thanks again

Pat

 

[Joan Wild]

Perhaps it's
http://support.microsoft.com/?kbid=236952

Sorting behaviour changed with Jet4.0

--
Joan Wild
Microsoft Access MVP

Thank you for the information! I've never done that before.

Anyhow, both expressions return a result of 1033.

When I go into Control Panel, I can find no direct reference to Locale and

Input Locale, but everything says US English.

Under the "Page Code Conversion Table" there are a lot of strange things

checked, some of which are greyed out. I am running Windows XP.

I guess I could just put a sort field in, use that as the primary sort,

then sort by part number. That just seems the long way round the barn and
should not be necessary.