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 (in Access '97)?

Answer

From: Shamil Salakhetdinov <shamil@marta.darts.spb.ru>
To: ACCESS-L <ACCESS-L@PEACH.EASE.LSOFT.COM>
Subject: Re: How to count characters in a field?
Date: 31 August 1998 22:21

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