Counting words in a collumn on a access table

[isaacalves27]

I ha ve a problem in access. I am a newbie so i can not help myself with all the things i ve read in the access forums. Beside that I need someone to help me with something very easy (i think) but i dont now how to do. So i will try to explain my problem. Imagine a table with to fields. Name and the second Dogs. Imagine:
Name.................Dogs
Mr. Peter...........Joseph; Blue; Winnie
Mr. Alexander......Jack

So the total in the table is 4 dogs. How can i have this number?

PS: I solvedthe problem in Excel but I need to do everything in Access. Thanks!!

 

[pdx_man]

Create a function and a procedure to do this. This way, you can call the function from a query to get the count of dogs for each record as well.

Public Function CountDelim(MyString As String, MyDel As String) As Integer
Dim varCount As Integer

    varCount = IIf(Len(Trim(MyString)) > 0, 1, 0)
    
    Do While InStr(MyString, MyDel) <> 0
         varCount = varCount + 1
         MyString = Mid(MyString, InStr(MyString, MyDel) + 1)
    Loop
    CountDelim = varCount

End Function

Public Sub CountAll()
Dim rst As DAO.Recordset
Dim TotCount As Integer

    Set rst = CurrentDb.OpenRecordset("Table1")
    TotCount = 0
    While Not rst.EOF
        TotCount = TotCount + CountDelim(Nz(rst!dogs, ""), ";")
        rst.MoveNext
    Wend
    MsgBox TotCount

End Sub

 

[neileg]

While pdx_man has solved your problem, the problem exists because of bad design. You should not store multiple pieces of data in a single field. The proper design would have a separate table for dogs with one record for each dog. Counting the number of dogs would then be very easy.