web 2.0


Count characters of a text in an Access data field

This tip shows how to count characters in a table field using VBA. The sample database uses three tables: tblASCII (for the ASCII characters), tblCount (to store the count) and tblText (which contains the text for which we want to count the characters). Download the sample file to have a better idea of the tip...

 IN A MODULE ENTER THE CODE
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'RUN THIS ONE FIRST
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub insertCount()
    Dim cn          As ADODB.Connection
    Dim rsAscii     As ADODB.Recordset
    Dim rsCount     As ADODB.Recordset
    Dim rsText      As ADODB.Recordset

    Set cn = CurrentProject.Connection
    Set rsAscii = New ADODB.Recordset
    Set rsCount = New ADODB.Recordset
    Set rsText = New ADODB.Recordset

    SQLAscii = "tblAscii"
    SQLCount = "tblCount"
    SQLText = "tblText"   

    rsAscii.Open SQLAscii, cn, adOpenKeyset, adLockOptimistic, _
         adCmdTable
    rsCount.Open SQLCount, cn, adOpenKeyset, adLockOptimistic, _
         adCmdTable
    rsText.Open SQLText, cn, adOpenKeyset, adLockOptimistic, adCmdTable

    If Not rsText.BOF Then rsText.MoveFirst

    While Not rsText.EOF
        IDText = rsText.Fields("IDText").Value
        rsAscii.MoveFirst
        While Not rsAscii.EOF
            total = countChar(rsText.Fields("Text").Value, _
                rsAscii.Fields("Character"))

            If total > 0 Then
                With rsCount
                    .AddNew
                    .Fields("IDText").Value = IDText
                    .Fields("countChar").Value = _
                       rsAscii.Fields("Character")
                    .Fields("Count").Value = total
                    .Update
                End With
            End If
            rsAscii.MoveNext
        Wend

        rsText.MoveNext
    Wend

    rsText.Close
    rsAscii.Close
    rsCount.Close

    Set rsCount = Nothing
    Set rsText = Nothing
    Set rsAscii = Nothing

    cn.Close
    Set cn = Nothing

End Sub
 

Sub insertAscii()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset

    Sql = "tblASCII"

    rs.Open Sql, cn, adOpenKeyset, adLockOptimistic, adCmdTable

    For i = 33 To 126
        With rs
            .AddNew
            .Fields("Character") = Chr(i)
            .Update
        End With
    Next
End Sub


Function
countChar(txt As String, Character As String) As Long

    For i = 1 To Len(txt)
        letter = Mid(txt, i, 1)

        If Asc(letter) <> Asc(Character) Then
             newText = newText & letter
        End If
    Next
    countChar = Len(txt) - Len(newText)
End Function
 


 

DOWNLOAD SAMPLE FILES HERE

Tags: , , ,

Microsoft Access | Microsoft Access - VBA

Comments

Comments are closed