Finding records containing uppercase letters

[csdlman]

I have a very simple table containing text string values and I need to isolate those records that contain one or more uppercase letters. Is there a handy way to do this, aside from the totally inelegant method of individually testing for each letter?



Thanks!

[JoeM]

One way is to create your own User Defined Function to do this, like this:

Function HasCaps(myString As Variant) As Boolean
'   Check for the existence of one or more caps in a string

    Dim i As Long
    Dim num As Integer

'   Set initial value to false
    HasCaps = False
    
'   Exit if no entry
    If Len(myString & "X") = 1 Then Exit Function
   
'   Loop through all characters
    For i = 1 To Len(myString)
'       Get ASCII code of character
        num = Asc(Mid(myString, i, 1))
'       If ASCII code between 65 and 90, it is a cap
        If (num >= 65) And (num <= 90) Then
            HasCaps = True
            Exit Function
        End If
    Next i

End Function

Then you would use this like any other function in your query, i.e.
CapsCheck: HasCaps([FieldName])

This will return TRUE (or -1) if the entry has any caps, and FALSE (or 0) if it does not.

[Minty]

This thread has a very cunning way of doing this
https://access-programmers.co.uk/for...ad.php?t=61453

By comparing the string to itself converted to Lower case.

[JoeM]

This thread has a very cunning way of doing this
https://access-programmers.co.uk/for...ad.php?t=61453

By comparing the string to itself converted to Lower case.

Very clever! That is certainly simpler than creating a UDF.

[csdlman]

Thank you, this is helpful.