Find the nth Occurrence of one string in another in Access

[Guest]

(ACCESS 2000)- In a query I want to dynamically calculate how many times a
character occurs in a string. Like Ones=OCCUR(AnyString,"1") - Any Ideas
please!
Here is a code Equivalent:-

Ones=0
AnyString="122211221"
For I = 1 to Len(AnyString)
If Mid(AnyString,I,1)="1" Then Ones=Ones+1
Next I
NB - If not Please Mr Gates include such a function in your next release.

 

[arthurjr07]

Put your code in a function.
like this and put that function in a
module.
*******************************************************
Public Function OCCUR(strSearch as string, _
strLook as string ) _
As Integer
Dim ones as integer
ones = 0
For I = 1 to Len(strSearch)
If Mid(strSearch,I,1)= strLook Then Ones=Ones+1
Next I

OCCUR = ones
End Function
*******************************************************************

After you have created and saved that function
you can now use that in your SQL statement

SELECT OCCUR(AnyString,"1") as ONES
FROM TABLENAME

HTH

 

[Albert D.Kallal]

Well, you can get a count by using

UBound(Split("122211221", "1"))

So, the above would return a value of 4

And,
UBound(Split("122211221", "12"))

Would return a vlaue of 2


I would suggest you build a public function, and then you can use it
anywhere in ms-access anyway.

In other words, place the following code in a standard module:

Public Function CountStr(str As String, strDelim As String) As Integer

' this routine returnds the number of occrancines of a string
' of charatres

' Parms are:
' str - the string to search
' strDelim - the string to find and count in the above


CountStr = UBound(Split(str, strDelim))



End Function

Now, any ime you need a count, you can go

CountStr("abcabcabc","c")

So, build the funciton yourself, and you will not have to wait for Gates to
build it....

 

[Allen Browne]

Another option might be to use InStr() in a loop until you get a zero result
or the nth occurance. Be sure to use the first argument of InStr() so you
are not starting from the beginning again each time.

A side issue might be how to handle the case where the search string
contains itself. For example if you are searching for the string "aa", in a
string that consists of "aaaaaaaaaa" which pair consistitues the 2nd find: 2
& 3 or 3 & 4?

 

[Guest]

Hi Albert

Is there an existing UBound and Split function in ACCESS? If so how do I
get more information on it? I know that OCCUR appears in other languages,
but not ACCESS.

I have used Arthur's 'Function' and this has provided one solution but if
UBound and Split exist this could probably give a more efficient solution in
terms of processing time.

Either way thank you for your reply. This is my first request for help and
I am extremely impressed with your generosity in giving your time and
intelect.

Cheers
Phil
(Alone Stranger)

 

[Jamie Collins]

(ACCESS 2000)- In a query I want to dynamically calculate how many times a
character occurs in a string. Like Ones=OCCUR(AnyString,"1") - Any Ideas
please!

This is an old trick: replace the search text (e.g. '1') with a string
of one less character (e.g. '') and test the resulting string's length
agaisnt the length of the original string e.g.

SELECT '122211221' AS my_col, LEN(my_col) - LEN(REPLACE(my_col, '1',
'')) AS ones

Jamie.

--

 

[Guest]

Hi Arthur

This is the first time I have made a request for information, and I am
extremely grateful for the effort you have made in replying.

I had tried to create a function as you explained but missed out the last
line 'OCCUR=ones' and therefore could not get the answer back into the
expression.

Your solution has completely satisfied my requirements. At the risk of
repeating myself I am most impressed and grateful for your time and effort.

Thank you
Phil
(Alone Stranger)

 

[Guest]

Hi Allen

I have been able to solve my problem with coding but I wanted to incorporate
an expression in a query to dynamically update a counter. Even so I have
found your coding useful for future reference.

As with Arthur and Albert I am extremely grateful for the time and effort
you have afforded me. Thank you very much

Cheers
Phil
(Alone Stranger)

 

[Guest]

Hi Jamie

Your suggestion has not solved the problem I have currently but it has given
me some ideas for both the use of queries (took a bit of thinking about) and
the replace command.

Thank you for your time and effort.

Cheers
Phil
(Alone Stranger)

 

[John Spencer]

They are both available in VBA. Split was added in Access 2000.

Albert's function will work as long as you pass it two strings. If you are
using this someplace where you might pass it a null or a zero-length string
as the first argument you might need to modify it slightly. Null will cause
an error and a zero-length string will return -1 (not zero).

Public Function fOccur(strIn, strDelimiter As String) As Long
Dim arStr As Variant, LngCount As Long

'Use Nz to force a value if strIn is null
LngCount = UBound(Split(Nz(strIn), strDelimiter, -1, vbTextCompare))

'Change -1 for nulls and zero length strings to 0
If LngCount = -1 Then LngCount = 0

fOccur= LngCount

End Function