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!!
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..
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
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" as A_COUNT FROM tblYOURTABLE
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 = ""
End If
End If
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.