Page 1 of 4 1234 LastLast
Results 1 to 15 of 49
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Need VBA (For Loop) to find a **repeated** 3-character string in a record... if so, mark record TRUE

    Experts:

    I need some assistance with developing a mechanism (maybe ForLoop in VBA) that *flags* records when a three-character string is found **at least twice** in a record.

    Attached database includes a table that contains some dummy data for illustration purposes only. Specifically, the table includes the following 6 records:

    Example Data (see JPG with repeated red 3-character strings):
    1. A chinchilla is a small rodent.
    2. Giraffes have long necks.
    3. The African elephant is large; it is also a very elegant mammal.
    4. There was a lion in the linen closet.
    5. The lion and the lioness are busy with their cubs.
    6. Dogs are man's best friend.

    Record Flagging (in a query):
    - Record #1's flag = TRUE; the 3-character string "chi" is found twice in the word "chinchilla".
    - Record #2's flag = FALSE; this sentence (record) does NOT contain a repeated 3-character string (at least as far as I can tell when looking at it).
    - Record #3's flag = TRUE; the 3-character string "ele" is found twice in the sentence (i.e., within 'elephant' as well as within 'elegant').


    - Record #4's flag = TRUE; the 3-character string "The/the" is found twice in the sentence (i.e., within 'There' as well as within 'the').
    - Record #5's flag = TRUE; the 3-character string "The/the" is found twice in the sentence (i.e., within 'The' as well as within 'their'); also, the 3-charcter string "lio" is found twice in the sentence (i.e., within 'lion' as well as within 'lioness').
    - Record #6's flag = FALSE; this sentence (record) does NOT contain a repeated 3-character string (at least as far as I can tell when looking at it).

    More details on the For Loop (based on record #1):
    - The loop takes the 1st three characters "A c" and it is only found once in the sentence.
    - The loop then takes the next three letters " ch" and it is only found once in the sentence.
    - The loop then takes the next three letters "chi" and it is found twice in the sentence; thus, the criteria are met and the loop terminates. Therefore, this record is flagged as TRUE.
    - Now, given the 1st record has been flagged, the loop transitions to the 2nd sentence (record).
    - Again, it starts with "Gir"... only found once.
    - Then, it takes, "ira"... only found once.
    ... etc.
    ... etc.
    - The loop terminates the 2nd record with "ks." Ultimately, nowhere in this sentence was a 3-charcter string repeated. Thus, the 2nd sentence is flagged as FALSE.

    My question:
    How can the above routine be established in VBA and then called in a query?

    Thousand thanks in advance!
    Attached Thumbnails Attached Thumbnails ExampleData.JPG  
    Attached Files Attached Files

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I almost wish I wasn't on my way out to dinner! Looks like an interesting one. Probably will require combination of Mid and Instr functions.
    Mid(yourStringVariable,i,3) wrapped inside of or passed to Instr to find the repeat, which would return a value if True. Then move on to next i (which after the first loop, would be 2, then 3 and so on). i could be based on Len(yourStringVariable) - perhaps minus 2 because there's no point in testing on the last 2 characters.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Micron -- thank you for the response... enjoy your dinner!

    I'd welcome any feedback you may have later on or tomorrow. Thank you in advance!!!

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Here you are Tom.
    @ Micron - here is your function - enjoy your dinner (I'm just starting to cook mine for the special lady of the house on International Women Day !
    Code:
    Public Function vcThreeChrTwice(strSentence As String) As Boolean
    Dim sToCheck As String, i As Integer
    vcThreeChrTwice = False
    If Len(strSentence) <= 3 Then Exit Function
    
    
    For i = 1 To Len(strSentence) - 2
        sToCheck = Mid(strSentence, i, 3)
        'If SF_count(strSentence, sToCheck) >= 2 Then vcThreeChrTwice = True: Exit Function 'Vlad using custom function
        If InStr(i + 3, strSentence, sToCheck) > 0 Then vcThreeChrTwice = True: Exit Function 'Micron's suggestion with InStr
    Next i
    
    
    End Function
    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  6. #6
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad:

    Wow... that's totally awesome! Many, many thanks for the prompt response. Please allow me to ask a follow-up question (or two).

    1. If I wanted to change it from a "3-character" to, e.g., "4-character" string, do I change the following lines:

    If Len(strSentence) <= 4 Then Exit Function
    sToCheck = Mid(strSentence, i, 4)
    If InStr(i + 4, strSentence, sToCheck) ...


    2. Is there a way to display "TRUE/FALSE" (or do I need to stick w/ the 0, -1)?


    3. You commented out "'If SF_count(strSentence, sToCheck) >= 2 "... is there a point in time as to when I may need to use "your line" (vs. Micron's)?

    4. Do you happen to have a source document (URL) which may provide me more info on this process?

    Thank you,
    Tom

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    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
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Absolutely fantastic... 'works like a charm.

    For the 2nd query (vcGroupChrRepeat) though I get an error on the sToCheck line when opening the query. I'm using your code as is with the following in my query: Expr1: vcGroupChrRepeat([Sentence],3,2)

    At that point, VBA error pops up w/ the following error on line "SF_count". Compile error Sub or Function not defined.

    I attempted to declare the SF_count via following mod... still not working though.
    Dim sToCheck As String, SF_count As String, i As Integer

    What's missing given the VBA throws an error on the SF_count line?

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have you imported the module and added the functions to it? Here is your updated sample with all three in one query.

    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    All that code and time to cook too! Nice work, Vlad.
    We went to the Keg restaurant and yes, Filet Mignon was very good.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Glad to hear, Keg has some nice steaks for sure. I went the sea food way, oysters (BC coast still in season), Coho sashimi, calamari in butter\Gewurztraminer. No complaints, probably much better than my code....

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  12. #12
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad -- gotcha, I see... thank you for the additional clarification.

    At this time, your solution does EXACTLY what I listed in my post. In hindsight though, I wish I had included one or two additional requirements. While they are not 100% critical, they fall into the "Better" / "Best" category. See attached JPG for illustration purposes.

    That said, is there any way the function could be (easily) modified to include
    a) which "group string" (3 or 4 or n characters in length) was found in the sentence and
    b) how was it/were they found?

    Please note that record #5 has multiple repeated character strings (i.e., "The/the" exists 3 times while "lio" exists 2 times).

    Again, I truly appreciate your assistance in this matter. I realized though that lengthy sentences/search criteria would make it somewhat difficult (for the naked eye) to follow which character string made it TRUE. Btw, I'll be more than happy to open a new post if you prefer.

    Thank you,
    Tom

    P.S. If any repeated groups (e.g., "the" and "lio" cannot be displayed together, then the frequency count of the first group 'the' would be fine too... in the BEST solution).
    Attached Thumbnails Attached Thumbnails Current, Better, Best.JPG  

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Tom,


    I'm not trying to be difficult, but just pointing out that ion appears twice in sentence 5 also. It may not be important -just an observation.

  14. #14
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    orange -- you are absolutely correct... "devil is in the detail" so to speak. Thank you for pointing it out! Looks like my eyes may not be considered 20/20 any longer).

    That said, is there a way to output the "strings" values into another column (in query)? While frequency of occurrence maybe a nice to have, it wouldn't be entirely necessary.
    So, potentially, for the 5th record, the 2nd column output may be something like: "the, lio, ion"

    Note, please keep in mind that the string output must be dynamic. I tried n = 8 and it returns 5th record as TRUE as well. In that case though, the string is "The lion" by itself.

    Any thoughts/recommendations how to accomplish it? Again, if preferred/recommended, I'll gladly open a new thread and post the cross-reference here.

  15. #15
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I suggest you keep things in this thread since it all seems to be on the topic "checking whether or not there exists at least X number of occurrences of a string within a larger string". (and what were/are the substrings and the number of occurrences)[new functionality]

    I'm sure it can be done -especially now that micron and vlad have enjoyed their suppers. It seems a useful function.

    Can you describe how it fits in your business?

Page 1 of 4 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 08-27-2020, 06:50 AM
  2. Replies: 8
    Last Post: 07-23-2017, 02:04 PM
  3. Replies: 3
    Last Post: 09-22-2016, 11:16 AM
  4. Find a record that contains a word in a string
    By knowshowrosegrows in forum Queries
    Replies: 2
    Last Post: 11-17-2015, 11:35 AM
  5. Replies: 2
    Last Post: 11-15-2013, 09:58 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums