Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Sort Text Field By Lower/Upper Case? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    How would I sort a text field in a query so that lower case values sort in fron of upper case values ?

    Example

    john
    John
    JOHN

    Thanks, John

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

    Re: Query Sort Text Field By Lower/Upper Case? (a2k (9.0.6926) SP-3 Jet 4.

    Put the following function in a standard module:
    <code>
    Function StrToHex(S As Variant) As Variant
    Dim Temp As String
    Dim C As Integer
    Dim I As Integer
    If varType(S) <> 8 Then
    StrToHex = S
    Else
    For I = 1 To Len(S)
    C = Asc(Mid(S, I, 1))
    Select Case C
    Case 65 To 90
    C = C + 32
    Case 97 To 122
    C = C - 32
    End Select
    Temp = Temp & Format(Hex©, "00")
    Next I
    StrToHex = Temp
    End If
    End Function
    </code>
    Say that you want to sort on a field FirstName. Add a calculated column to your query:
    <code>
    SortField: StrToHex([FirstName])
    </code>
    Clear the Show check box for this column and sort on it instead of on FirstName itself.

    Notes:
    The function is based on the one from ACC: How to Sort Records in Case-Sensitive ( ASCII) Order, modified to reverse the order of upper and lower case letters.
    The function doesn't take letters with accents such as

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Sort Text Field By Lower/Upper Case? (a2k (9.0.6926) SP-3 Jet 4.

    Thank you

Posting Permissions

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