Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.
    <code>
    SortCol: Replace([FieldName],"-","")
    </code>
    - 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
    <code>
    Replace([FieldName],"-","")
    </code>
    to the Sorting and Grouping window (with the correct field name substituted) and specify Ascending as sort order.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

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

    Thank you Hans <img src=/S/smile.gif border=0 alt=smile width=15 height=15>.

    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) <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    ... 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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Take care!
    Hasse

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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:
    <hr>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.<hr>
    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 <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

    Try this instead:
    <code>
    SortCol: Replace([FieldName],"-"," ")
    </code>
    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.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    SOLVED - Re: Sort including hyphens (-) (All (?))

    Thanks, Hans... also for the interesting info. Now I know the cause wasn't me <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.

    <font color=4682b4> 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!</font color=4682b4>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •