Hi Tom,
Here is the function for any number of characters:
Code:
Public Function vcGroupChrTwice(strSentence As String, iCharacters as integer) As String
Dim sToCheck As String, i As Integer
vcGroupChrTwice= "False"
If Len(strSentence) <= iCharacters Then Exit Function
For i = 1 To Len(strSentence) - (iCharacters -1)
sToCheck = Mid(strSentence, i, iCharacters)
'If SF_count(strSentence, sToCheck) >= 2 Then vcGroupChrTwice= "True": Exit Function 'Vlad using custom function
If InStr(i + iCharacters, strSentence, sToCheck) > 0 Then vcGroupChrTwice= "True": Exit Function 'Micron's suggestion with InStr
Next i
End Function
To use it in a query: vcGroupChrTwice([YourFieldTOCheck],4).
Please notice that I've changed to return the literal strings "True" and "False" (your point #2). With the commented out line I wanted to show the use of the custom function SF_Count which would have been the answer to your original post, both would return the same result, but mine might be a bit more flexible in case you want to run the code for strings that happen more than twice (you would simply say If SF_count(strSentence, sToCheck) >= 4 if you wanted to flag records where the group repeats four times). The included module has a lot of very useful string functions and I would recommend you to review them, it even has a test sub to show their usage.
As for the URL, sorry but I don't have any, do some searches for VBA string manipulation functions and you should get plenty of pages to study .
EDIT: here is another version that also takes the number of occurrences:
Code:
Public Function vcGroupChrRepeat(strSentence As String, iCharacters as integer,iOccurences as integer) As String
Dim sToCheck As String, i As Integer
vcGroupChrRepeat = "False"
If Len(strSentence) <= iCharacters Then Exit Function
For i = 1 To Len(strSentence) - (iCharacters -1)
sToCheck = Mid(strSentence, i, iCharacters)
If SF_count(strSentence, sToCheck) >= iOccurences Then vcGroupChrRepeat= "True": Exit Function 'Vlad using custom function
'If InStr(i + iCharacters, strSentence, sToCheck) > 0 Then vcGroupChrRepeat= "True": Exit Function 'Micron's suggestion with InStr -right now limited to 2 occurences
Next i
End Function
To use it for your initial settings (3 characters, 2 occurrences):vcGroupChrRepeat([YourFieldTOCheck],3,2)
Cheers,
Vlad