CODEWITHSUNDEEP
vbams-access
Key K
Key K

Reputation: 169

Microsoft Access/Count words

How I can count words in a Access column? Example I have 33 words "Rec" and 20 with "NoRec" and I want the number to display in two text-boxes, one for Rec and one for NoRec.

Upvotes: 0

Views: 1443

Answers (2)

PaulFrancis
PaulFrancis

Reputation: 5809

I do not think you need VBA to do this, you can make use of the TOTALS Query to get the desired result you want. 

SELECT 
    Sum(IIF(yourColumnName = "Rec", 1, 0)) As TotalRec,
    Sum(IIF(yourColumnName = "NoRec", 1, 0)) As TotalNonRec
FROM 
    YourTableName

Upvotes: 1

Paul Ogilvie
Paul Ogilvie

Reputation: 25286

An Access Column is a column of an Access Table. So you SELECT all rows of that table, get the column you want into a string and count the number of words in that string. Assuming the column is myColumn and the table is myTable:

Private Sub Test()
Dim dbs As Database
Dim rst1 As Recordset
Dim s As String

    Set dbs = CurrentDb
    With dbs
        ' Select all records
        Set rst1 = .OpenRecordset("SELECT myColumn FROM myTable;", dbOpenDynaset)
        While (Not rst1.EOF)
            s = rst1.Fields("myColumn")
            ' now analyse s
            '
            rst1.MoveNext
        Wend
        rst1.Close
    End With
    Set dbs = Nothing
End Sub

Upvotes: 1

Related Questions