Sort including hyphens (-) (All (?))

Topic

New Reply

How can I make the Access sorting order including hyphens?

E.g. I want:
AA-B
AAA
AAC

and not – which Access actually returns:
AAA
AA-B
AAC

Thanks in advance for all suggestions…
Hasse

Reply | Quote

Replies

In Access 2002 and later:

– Create a query based on your table (or query).
– Add a calculated column, replacing FieldName with the name of the field on which you want to sort.

SortCol: Replace([FieldName],"-","")

– Clear the Show check box for this column.
– Set the sort order for this column to Ascending.

In a report, you can add the expression

Replace([FieldName],"-","")

to the Sorting and Grouping window (with the correct field name substituted) and specify Ascending as sort order.

Reply | Quote

Thank you Hans .

Fwiw – the reason of my post: for a couple of years now, I’ve been using a (fixed) sorted list of our local partners. For some reason this list’s sorting order takes into account the hyphens.
Now I wonder if it’s MS Office which has changed it’s sorting order since (I started in Office 97 Excel or Access) or if my memory is fooling me (and then I must have made the order changes manually when I started to use that list at the time)
… as now both Access & Excel are ignoring the hyphen.
If anyone can enlighten me, please do… I’ll come over it if nobody knows… but knowing would be nice
Take care!
Hasse

Reply | Quote

On re-reading your original post, I see that my previous reply won’t do what you want. Sorry about that.

Access and Excel ignore hyphens and apostrophes, this is from the Excel help subject on default sort orders:[indent]

---

Apostrophes (‘) and hyphens (-) are ignored, with one exception: If two text strings are the same except for a hyphen, the text with the hyphen is sorted last.

---

[/indent]For Access, tha change was introduced in version 4 of the Jet Engine, with Access 2000. See PRB: Sort Order Has Changed with Microsoft Jet version 4.0.

My reply simply ignored hyphens too

Try this instead:

SortCol: Replace([FieldName],"-"," ")

where FieldName is the name of the field you want to sort on. This replaces hyphens with a space, and a space is sorted before all letters.

Reply | Quote

Thanks, Hans… also for the interesting info. Now I know the cause wasn’t me .

Fwiw: now that I know how it works (and with my hope vanished that I just needed to change some general option), I might consider another option… We exchange data back and forth through excel quite often. So, rather than implementing this solution to all queries used for data exchange… it’s probably less a burden to change my default (excel) list’s sort order (on which all overview”s,… are based) according to Office’s current default. The latter will create an inconsistency between old and new lists, but it’ll be more ‘stable’ and less rectification work – e.g. when users for some reason would sort their list themselves… (anyway) – Thanks!

Reply | Quote