Count different characters in a field

[Beng]

Hi there! Instead of trying to count within a string, I try to count in a table field to find out how many are upper case & lower case characters, how many are numbers, and how many are other characters. I use the code below but the answer I checked, it ain't right. I seem to hit a block now and going no where. Hope to be able to a help in this forum.

Many thanks.

Sub count_in_a_field()
Dim data As String
Dim data_length As Integer
Dim char_loop As Integer
Dim number_count As Integer
Dim upper_case_count As Integer
Dim lower_case_count As Integer
Dim other_count As Integer

Dim conn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim ssql As String
Dim originalLength As Integer

Set conn = CurrentProject.Connection
ssql = "Select * From Table1"
rs.Open ssql, conn, adOpenKeyset, adLockOptimistic

data = rs.Fields("Field1")
data_length = Len(data)

number_count = 0
upper_case_count = 0
lower_case_count = 0
other_count = 0

Do Until rs.EOF

For char_loop = 1 To data_length
Select Case Asc(Mid(data, char_loop, 1))
Case 48 To 57
number_count = number_count + 1
Case 65 To 90
upper_case_count = upper_case_count + 1
Case 97 To 122
lower_case_count = lower_case_count + 1
Case Else
other_count = other_count + 1
End Select
Next char_loop

rs.MoveNext
Loop

Debug.Print number_count & " numbers found"
Debug.Print upper_case_count & " upper case letters found"
Debug.Print lower_case_count & " lower case letters found"
Debug.Print other_count & " other characters found"
rs.Close
Set rs = Nothing
Set conn = Nothing
End Sub

 

[stopher]

The following statements need to be inside the DO loop otherwise the value will only be read once:
data = rs.Fields("Field1")
data_length = Len(data)

So..

Sub count_in_a_field()
Dim data As String
Dim data_length As Integer
Dim char_loop As Integer
Dim number_count As Integer
Dim upper_case_count As Integer
Dim lower_case_count As Integer
Dim other_count As Integer

Dim conn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim ssql As String
Dim originalLength As Integer

Set conn = CurrentProject.Connection
ssql = "Select * From Table1"
rs.Open ssql, conn, adOpenKeyset, adLockOptimistic

number_count = 0
upper_case_count = 0
lower_case_count = 0
other_count = 0

Do Until rs.EOF
[COLOR="Red"]    data = rs.Fields("Field1")
    data_length = Len(data)[/COLOR]
    For char_loop = 1 To data_length
        Select Case Asc(Mid(data, char_loop, 1))
            Case 48 To 57
                number_count = number_count + 1
            Case 65 To 90
                upper_case_count = upper_case_count + 1
            Case 97 To 122
                lower_case_count = lower_case_count + 1
            Case Else
                other_count = other_count + 1
        End Select
    Next char_loop
    rs.MoveNext
Loop

Debug.Print number_count & " numbers found"
Debug.Print upper_case_count & " upper case letters found"
Debug.Print lower_case_count & " lower case letters found"
Debug.Print other_count & " other characters found"
rs.Close
Set rs = Nothing
Set conn = Nothing
End Sub

Also note how I've indented the different blocks. This is good practice and makes code much easier to read and debug.

hth
Chris

 

[Beng]

Many thanks to Chris.