Number of letters in a field

[diakis]

I have a field with 3 records: 1=Henning, 2=Daniel and 3=Santiago. I want to do a field in a query to give me the number of letters in each one record but the same letter to sum once. The field in the query must give: 1=5, 2=6 and 3=7.
Thanks

 

[Trevor G]

You can't do this with Functions in Access, you would need VBA to do this.

 

[arnelgp]

put this in any Module:

Public Function CountDistinctLetter(ByVal strSource As Variant) As Integer

    Dim strTemp As String
    Dim intLen As Integer
    Dim intLoop As Integer
    Dim strChar As String
    
    strSource = strSource & ""
    intLen = Len(strSource)
    
    For intLoop = 1 To intLen
        strChar = Mid(strSource, intLoop, 1)
        ' only letter and digit
        ' remove this IF if you also want to test for these characters
        If InStr(1, " .,;!@#$%^&*()_-:|\'<>?/\=0123456789" & """", strChar) = 0 Then
            If InStr(1, strTemp, strChar) = 0 Then
            'If InStr(1, stretmp, strChar, vbTextCompare) = 0 Then
                strTemp = strTemp & strChar
            End If
        End If
    Next intLoop
    CountDistinctLetter = Len(strTemp)
End Function

In your query:

TextCount: Val([yourField] & "") & "=" & CountDistinctLetter([yourField])

 

[diakis]

Thanks again. It works very well