Thread: Select the highest and lowest alpha character

1. 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. 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

3. Perfect!!

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

Jim

4. 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.

Posting Permissions

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