Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort Criteria (Access 97)

    Hi

    I have a table containing a field of chemical names. As most will be aware chemical names includes numbers, hyphens, greek characters (eg alpha as a word not symbol), other symbols eg brackets, and certain other characters eg bis, tau, which should be ignored when sorting the chemical name.

    So it should sort as follows:

    bis(2-ammonio-2-deoxy-d-glucose) sulphate
    benzene
    gamma-BHC
    3-bromo-1-chloro-5,5-dimethylhydantoin

    I can think of a couple of ways, and while realising there possibly may be performance issues and no method will be perfect, it would make life easier when doing reports and forms. However, I'm hoping there is already some code I can use for such a purpose. Any help appreciated.

    Thanks & Regards
    WTH

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Sort Criteria (Access 97)

    Why you will need to do is to have another field in the table and create this off the chemical names but without the specials. Then sort on that field.

    Another way is to write a special Function that will drop all the odd characters from the name, and sort on this.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Criteria (Access 97)

    Hi

    Yes, I was also thinking along those lines. However, being ever hopeful I thought why do it twice if someone has already got a niffy function to do this and is happy to display the code.

    Regards
    WTH

  4. #4
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Criteria (Access 97)

    Hi

    This is the code I finally developed. It is split between two parts. First, remove greek letters and the second part to remove all non letters. The latter is done by using the chr ansi function (not sure whether this is appropriate).

    Not sure whether it is the best method, so would appreciate any suggestions.

    Function ChemicalSort(ByVal ChemicalName As String) As String
    Dim Temp As String, P As Long
    Dim C As Long
    Dim Rng(32 To 64, 91 To 96, 123 To 221) As Long
    Dim Greekvar As Variant
    Dim Greek As Variant
    Dim abbrevar As Variant
    Dim abbrev As Variant

    Dim Rngnum As Long
    Dim Resultvar As Variant

    Dim CntAbbrev As Long
    Dim CntGreek As Long
    Temp = ChemicalName

    Dim Cnt As Long

    'Remove greek letter names only when followed by bracket, comma, or hyphen
    abbrev = Array("(", ",", "-", "[")
    Greek = Array("bis", "alpha", "gamma", "beta", "delta", "tau", "lambda", "zeta", "sigma")
    Cnt = 0
    CntAbbrev = UBound(abbrev) + 1
    CntGreek = UBound(Greek) + 1

    ReDim Result((CntAbbrev * CntGreek) - 1) As Variant
    For Each Greekvar In Greek
    For Each abbrevar In abbrev
    Result(Cnt) = (Greekvar & abbrevar)
    Cnt = Cnt + 1
    Next
    Next


    For Each Resultvar In Result
    P = InStr(Temp, Resultvar)
    Do While P > 0
    Temp = left(Temp, P - 1) & "" & _
    Mid(Temp, P + Len(Resultvar))
    P = InStr(P + Len(""), Temp, Resultvar, 1)

    Loop
    Next Resultvar

    Rngnum = 1
    For Rngnum = 1 To 3
    For C = LBound(Rng, Rngnum) To UBound(Rng, Rngnum)
    P = InStr(Temp, Chrę)
    Do While P > 0
    Temp = left(Temp, P - 1) & "" & _
    Mid(Temp, P + Len(Chrę))
    P = InStr(P + Len(""), Temp, Chrę, 1)
    Loop
    Next C
    Next Rngnum

    ChemicalSort = Temp
    End Function


    Regards
    WTH

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

    Re: Sort Criteria (Access 97)

    Some remarks:
    <UL><LI>When replacing a string by nothing, there is no need to concatenate "".
    <LI>There is no need to construct all those extra arrays.
    <LI>In the second part, instead of looping through all the characters to be removed, loop through the characters of the chamical name.[/list]Here is a modified version:

    Function ChemicalSort(ByVal ChemicalName As String) As String
    Dim Temp As String
    Dim Temp2 As String
    Dim ToRemove As String
    Dim P As Long
    Dim Greekvar As Variant
    Dim Greek As Variant
    Dim Abbrevar As Variant
    Dim Abbrev As Variant

    Temp = ChemicalName

    'Remove greek letter names only when followed by bracket, comma, or hyphen
    Abbrev = Array("(", ",", "-", "[")
    Greek = Array("bis", "alpha", "gamma", "beta", "delta", "tau", "lambda", "zeta", "sigma")

    For Each Greekvar In Greek
    For Each Abbrevar In Abbrev
    ToRemove = Greekvar & Abbrevar
    P = InStr(Temp, ToRemove)
    Do While P > 0
    Temp = Left(Temp, P - 1) & Mid(Temp, P + Len(ToRemove))
    P = InStr(P, Temp, ToRemove)
    Loop
    Next
    Next

    For P = 1 To Len(Temp)
    Select Case Asc(Mid(Temp, P, 1))
    Case 65 To 90, 97 To 122
    Temp2 = Temp2 & Mid(Temp, P, 1)
    End Select
    Next P

    ChemicalSort = Temp2
    End Function

  6. #6
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Criteria (Access 97)

    Hi Hans

    Many thanks for the more refined code (as usual I have over complicated things). However, I did learn a lot about arrays, which I have not really used before, so from my perspective it was time well spent.

    Regards
    WTH

Posting Permissions

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