How to count characters in a field


Question

Could anybody tell me how could I count all characters, excluding spaces, in all records in a text field?

Answer

Mireille,

To solve you task you can use this function:

Public Function CharsCount(ByVal vvarTxtFld As Variant) As Long
    On Error GoTo CharsCount_err
    
    Dim lngIdx As Long
    Dim lngRet As Long
    Dim lngPos As Long
    
    lngRet = Len(vvarTxtFld)
    
    For lngIdx = 1 To lngRet
        Select Case Mid(vvarTxtFld, lngIdx, 1)
        Case " ":  lngRet = lngRet - 1
        Case Else:
        End Select
    Next
    CharsCount = lngRet
CharsCount_Exit:
    Exit Function
CharsCount_err:
    Resume CharsCount_Exit
End Function

this way:

SELECT Sum(CharsCount([Address])) AS AddressCharsQty FROM Customers

HTH,
Shamil


Copyright 1999-2008 by Shamil Salakhetdinov.  All rights reserved.