Need to search for string in table and return count.. help!

[mikeyboy1]

I need help!! I have a table in which im trying to find the count of a string in a specific column. In other words, im trying to count how many times "mike" shows up in a specific column.Can anyone help!!

 

[TLady]

Select count(Field) from Table
where Field="Mike"

TIA

 

[mikeyboy1]

Thanks for the reply. Each record is one very long string ie: "W|W|UEPBX|3|23799|mike|Non-Designed|mike|NDS-SB|No" I
am trying to get a total count of "mikes" in the column.
I have been trying to create a function that will do it but have been at a loss..

 

[TLady]

Does every string has equal number of characters before and between "mikes" ?

 

[mikeyboy1]

no. Thats what has got me stumped.

 

[TLady]

OK, you can reveal what tools you are using
About time now

 

[Rolliee]

The easiest way is probably with code.

dim rs as dao.recordset ,nn as long
set rs = me.recordsetclone
rs.movefirst
do while not rs.eof
if instr("Mike", rs![YyField] then nn = nn + 1
rs.movenext
loop
rs.close
set rs = nothing
msbox nn ' or whatever

rollie@bwsys.net

 

[bdmclennan]

Mikeyboy,

Try this function which you can call from your query:


Public Function fncCountField(strSentence As String, strSearch As String) As Long
Dim intPos As Integer 'You position within the string

If Len(fncCountField) = 0 Or Len(strSentence) = 0 Then
fncCountField = 0
Exit Function
End If
'Initialise IntPos
'-----------------
intPos = InStr(1, strSentence, strSearch)
'Keep looping until you have found all instances
'===============================================
Do While intPos > 0
fncCountField = fncCountField + 1 'Increment each time we find mike
intPos = InStr(intPos + 1, strSentence, strSearch)
Loop
End Function


the SQL statement should look like this:

SELECT fncCountField([YOURFIELD],"MIKE&quot as A_COUNT FROM tblYOURTABLE


Hope this helps

Bruce

 

[mikeyboy1]

Thanks to all that responded. i ended using this function which counts the amount of delimeters. after getting the results i just counted the rows.

Function xg_GetSubString(mainstr As String, n As Integer, delimiter As String) As String
'* Get the "n"-th substring from "mainstr" where strings are delimited by "delimiter"
Dim i As Integer
Dim substringcount As Integer
Dim pos As Integer
Dim strx As String
Dim val1 As Integer
Dim w As String

On Error GoTo Err_xg_GetSubString

w = ""
substringcount = 0
i = 1
pos = InStr(i, mainstr, delimiter)
Do While pos <> 0
strx = Mid(mainstr, i, pos - i)
substringcount = substringcount + 1
If substringcount = n Then
Exit Do
End If
i = pos + 1
pos = InStr(i, mainstr, delimiter)
Loop

If substringcount = n Then
xg_GetSubString = strx
Else
strx = Mid(mainstr, i, Len(mainstr) + 1 - i)
substringcount = substringcount + 1
If substringcount = n Then
xg_GetSubString = strx
Else
xg_GetSubString = &quot;&quot;
End If
End If

Exit Function

Err_xg_GetSubString:
MsgBox &quot;xg_GetSubString &quot; & Err & &quot; &quot; & Err.Description
Resume Next

End Function