Counting a particular name in a text field with several names.

[Global1003]

I have a text field that lists names seperated by the underscore (_). There may be up to five names in the filed. (Pat_Bob_Jay) How can I get a count a particular name (Pat)that is in that text field for the master Query.

[Seth Schrock]

It is not very easy when you have it designed with multiple names in a single field. You should look at Database Normalization and Table Structures. If (and only if) you can't do anything about having multiple names, then there is an option, but not one that can be 100% accurate 100% of the time. For example, if you search a string for "Pat" it will find "Patty", "Patricia", "Patrick", etc. If you start the list of names with an underscore and end the list with an underscore (ie "_Pat_Bob_Jay_"), then you can find it because you can search for "_Pat_". Anyway, here is the solution that I would recommend if you can't do anything about the list:

Public Function NameCount(strSearch As String) As Integer
Dim intCount As Integer
Dim intStart As Integer
 
intCount = 0
intStart = 0
 
Do While InStr(intStart, Me.TextBox_Name, strSearch) <> 0
    intCount = intCount + 1
    intStart = InStr(intStart, Me.TextBox_Name, strSearch) + 1
Loop
 
NameCount = intCount
End Function

When you call this function, then you would just pass the name that you are searching for as the functions argument.

[Rabbit]

Prepend and append an underscore to the value and then you can use the LIKE operator.

[Seth Schrock]

@Rabbit Can you count the occurrences of the name using the LIKE operator?

[Rabbit]

I am making the assumption that the name only appears once per row.

[Seth Schrock]

Maybe I'm misreading the question, but "How can I get a count a particular name (Pat)that is in that text field for the master Query." sounds like there might be the same name twice.

@Global1003 can you specify what exactly you are wanting?

[jforbes]

If you are looking to get a count within a String, you can use the following neat trick:

Public Function occurranceCount(ByRef sFindIn As String, ByRef sFind As String) As Long
    Dim iFindLen As Integer
    iFindLen = Len(sFind)
    If iFindLen > 0 Then
        occurranceCount = (Len(sFindIn) - Len(Replace(sFindIn, sFind, ""))) / iFindLen 
    End If
End Function
 

It replaces the value you are looking for in the String, then compares the length of the result to the original string and sees what the difference happens to be.

EDIT
Found a RegEx solution for count within a line:

Public Function countRXMatches(ByVal SourceString As String, ByVal Pattern As String)
    Dim oMatch As Object
    Dim RegExp As Object
 
    Set RegExp = CreateObject("vbscript.regexp")     
    With RegExp
        .MultiLine = True
        .IgnoreCase = True
        .Global = True
        .Pattern = Pattern
        countRXMatches = .Execute(SourceString).Count
    End With
End Function

[NeoPa]

I'm pretty sure the question is to count the number of rows that include the string. Rabbit's suggestion, though a little short on padding, is perfect for this.

Unfortunately, for it to work reliably, you'd need SQL similar to :

...
WHERE ('_' & [SearchedField] & '_' Like '*_' & 'Pat' & '_*')

In this example the name 'Pat' has been used as a literal string but other references could also be use, such as a reference to a control on a form or even a field in a record.

NEVERTHELESS:
Don't be tempted to ignore Seth's point about Normalisation just because it's possible to work without it. It sounds like the design you're working with is crying out to be done properly.