Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort order questions (97/SR-2)

    Hi,
    Two for the price of one <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    1) Is there anywhere that defines/shows what the sort order is for the various settings eg General, Traditional Spanish, Dutch etc under Tools>Options, General (other than making a table with chrs 32 up and sorting it)?

    2) (The real Q) I need to sort a table of part numbers by true alpha-numeric (ie A-Z then 1-9). Can I define my own sort order?

    At the moment I've got a hidden field that gets filled with a text string based on substituting each character in the part number with a character that makes the sort work.

    this is done by a huge Select Case covering each character (attached text file)


    Is there a more elegant/easier/more understandable way of doing this? (I've inherited this db from someone else & I fully admit I don't understand 1/2 of whats going on!!)

    Thanks in advance
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Sort order questions (97/SR-2)

    In response to Q. 1, re: database Collating Order, create a table of all ASCII characters & sort it. This can be done via code (can provide example if interested). I know of no way to create a "custom" Collating Order. In response to Q. 2, you may be able to simplify things with a couple of user-defined functions as shown in this example:

    Public Function GetSortNum(ByVal strChar) As Integer

    Dim intChar As Integer
    intChar = Asc(strChar)

    Select Case intChar
    ' Upper/lower case non-foreign alpha characters:
    Case 65 To 90, 97 To 122
    GetSortNum = 1
    Case 48 To 57 ' Numeric
    GetSortNum = 2
    Case Else ' Symbols & foreign alphabetic
    GetSortNum = 3
    End Select

    End Function

    Public Function GetSortString(ByVal strTxt As String) As String

    Dim i As Integer
    Dim strChar As String
    Dim strTemp As String

    For i = 1 To Len(strTxt)
    strChar = Mid(strTxt, i, 1)
    strTemp = strTemp & GetSortNum(strChar) & strChar
    Next i

    GetSortString = strTemp

    End Function

    Note if you have a lengthy Select Case statement, it will execute quicker if testing for numerical rather than text values. Example of use in query to sort Part No field in "custom" order:

    SELECT PART_ID
    FROM TABLE1
    ORDER BY GetSortString([PART_ID]);

    This will sort the PART_ID field as shown in attached illustration, by alpha then numeric then non-alphanumeric (including foreign characters) order. If there are exceptions then sample funtion can be modified accordingly, I tried to keep it simple. Depending on size of table there may be slight delay when query is opened; as shown in SQL example above, do NOT include the calculated field with function in SELECT cause; this will slow down query. Maybe someone knows a more efficient approach for this. This will not sort in case-sensitive order; that can be accomplished with a similar function (can provide example if interested).

    If using later version of Access, recommend take a look at this MSKB article that describes differences in Jet 3.5 (A97) and Jet 4.0 (A2K & later) sort order:

    <A target="_blank" HREF="http://support.microsoft.com/default.aspx?scid=kb;en-us;236952">Sort Order Has Changed with Microsoft Jet version 4.0
    </A>

    HTH
    Attached Images Attached Images

  3. #3
    New Lounger
    Join Date
    Jan 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort order questions (97/SR-2)

    Thanks Mark,
    Your response & advice is much appreciated.
    The link to the knowledge base article was also v useful & feedback from other sources tends to agree with the principle of ignoring special characters when sorting so that ABC123 and ABC-123 are considered equal.
    I have asked my customer for their preferences.
    Regards

Posting Permissions

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