Count uppercase letters

[Misha]

Hello everybody,



I have field in one form with number of letters in upper and lower case with spaces. I need to count number of upper case letters only and put the number in different field. I found following code:

Me.Text47 = Len([Work Order])


"Work Order" - field with letters to be count
"Text47" - field, where result must put.

But this code counts all letters with spaces. Can you please help to change this code.

Thanks

[RuralGuy]

Try this UDF in a module:

Option Compare Binary
Option Explicit

Public Function CountUpper(strIn As String) As Long
'-- Count all of the UpperCase letters in strIn
   Dim offset As Long
   For offset = 1 To Len(strIn)
      CountUpper = CountUpper + IsUpper(Mid(strIn, offset, 1))
   Next offset
End Function

Public Function IsUpper(strIn As String) As Integer
   Select Case strIn
      Case "A" To "Z"
         IsUpper = 1
      Case Else
         IsUpper = 0
   End Select
End Function

[Misha]

Thanks a lot, it helped.

[RuralGuy]

Glad we could help.

[Misha]

Sorry for late reply, but I just figured out that when field with letters is empty, so instead to get "0" in second field or have just empty field I get error.

[RuralGuy]

try:

Option Compare Binary
Option Explicit
Public Function CountUpper(strIn As String) As Long
'-- Count all of the UpperCase letters in strIn
   Dim offset As Long
   If Len(strIn & "") > 0 Then
      For offset = 1 To Len(strIn)
         CountUpper = CountUpper + IsUpper(Mid(strIn, offset, 1))
      Next offset
   Else
      CountUpper = 0
   End If
End Function

Public Function IsUpper(strIn As String) As Integer
   Select Case strIn
      Case "A" To "Z"
         IsUpper = 1
      Case Else
         IsUpper = 0
   End Select
End Function

[Misha]

Now I get same error if the field is empty and another error if there any characters -

[RuralGuy]

Post the entire module between #code tags# in advanced.

[Misha]

Thank you for your help, this is the code I used with module you provided:

length = Len([Work Order])

If length > 0 Then Text204 = CountUpper([Work Order]) Else Text204 = ""

Now it works with letters and when the field is empty.