Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post
    Hi,

    I have various strings:
    A
    C:F
    C/F
    A/B/D/E
    etc.

    How can I select the highest alpha character and the lowest alpha character from a string?

    If the string is:
    A/B D-G F:M

    then the lowest character is A and the highest character is M. I need to ignore all punctuation, numbers, spaces etc. It's only A-Z upper case I'm interested in.

    Thanks
    Jim

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I think you are going to need a Custom VBA solution to this.
    Are you needing to Pick them up Individually or together.
    Would a Custom Function Do it

    The Function Below will return the MIN and MAX Capital Letters as a 2 character string from any input string

    I am sure there is a better solution but I'll leave that to someone cleverer than me.

    Code:
    Function GetMinMax(varString) As String
    
    Dim strChar As String, intChar As Integer
    Dim strMin As String, strMax As String, intASC As Integer
    Dim intMin As Integer, intMax As Integer
    Dim strMM As String
    
    strMin = ""
    strMax = ""
    intASC = 0
    intMin = 91
    intMax = 64
    
    If IsNull(varString) Then
        strMM = ""
    Else
        For intChar = 1 To Len(varString)
            strChar = Mid(varString, intChar, 1)
            intASC = Asc(strChar)
            If intASC >= 65 And intASC <= 90 Then
              If intASC < intMin Then
                intMin = intASC
              End If
              If intASC > intMax Then
                intMax = intASC
              End If
            End If
        Next
    End If
    
    strMin = Chr(intMin)
    strMax = Chr(intMax)
    
    strMM = strMin & strMax
    
    GetMinMax = strMM
    
    End Function
    Once placed in a Module in the database, you can call it from anywhere

    e.g.

    GetMinMax([FieldToCheck]) in a query
    or =GetMinMax([Field To Check]) in Form or Report etc
    Andrew

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post
    Perfect!!

    Slows it down a lot but I can live with that, thanks very much

    Jim

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Unfortunately SPEED is the issue with a lot of Custom VBA Functions.
    Not sure if putting it inside an ADD-IN would make it any faster.
    Andrew

Posting Permissions

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