Counting a record that has slashes

[mouseman]

I have an access database that lists names. Also the names are seprated by slashes.
For Example

1st record, Mike Doe/Rob Doe/Jay Doe
Second Record, Jane Doe/Mark Doe
Third Record, Adam Doe

Is there a way to count those records so it says 6. I tried a number of ways, but i come up with 3.

Thanks for your help

 

[AnalystDBA]

Hey mouseman ,

This should give you a count of the names in your table

create the following query
SELECT Sum(findtext([Your Name Field Here],'/')+1) AS NameCount
FROM [Table Name Here];

then create the following function in a standard module.

Option Compare Database
Option Explicit

Function FindText(StrTxt As String, FindTxt As String)

Dim i
Dim n
Dim cnt
Dim txtFound

i = Len(StrTxt)
n = 1
cnt = 0
txtFound = 0

Do While n < i
n = InStr(n, StrTxt, FindTxt) + 1

If n = 1 And cnt = 0 Then
FindText = 0
Exit Function
End If

If n = 1 And txtFound = 1 Then
Exit Do
End If


txtFound = 1
cnt = cnt + 1
Loop

FindText = cnt

End Function

the find text function counts the number of slashes in the name field in your record
the query then sums them to give you the total number of names.

Cheers,

AnalystDBA

 

[CosmoKramer]

To expand on AnalystDBA's post:

You might want to account for potential null name fields. Add this to AnalystDBA's select statement:

SELECT Sum(findtext([Your Name Field Here],'/')+1) AS NameCount
FROM [Table Name Here]
WHERE (((table.field) Is Not Null));

 

[mouseman]

Hi

Thanks for your help but I need some direction.

The first thing I did was type the formula, SELECT Sum(findtext([Broker],'/')+1) AS NameCount FROM [Broker Meetings] in a text box in a Access report. When I previewed the report, I got a syntax error, missing expression. Then I created a function you specified. I was not sure how to link this function to the report. If you can help me further it will be appreciated. Thanks again.