Results 1 to 11 of 11
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Need VBA (or query) which extracts multiple keywords containing an "_" in the record

    Experts:

    I need some assistance with either a) creating a query or b) using some VBA which will extract all *entire* words that include an underscore ("_") in a record.

    The record may include carriage returns as well as duplicate occurrences of a particular keyword. Below shows and example of the raw data, the output of the keywords (w/ duplicates) as well as the string output without duplicates.

    Ideally, I would like to get to the output of the "2nd string" (w/o duplicates). Also, each "found keyword" should be separated by a semi-colon (or comma).

    My question: How can the output below accomplished via a query or VBA? I have included a screenshot containing 3 different example strings (with different lengths) and their associated outputs. Also, attached is the sample DB containing 3 example records.


    Code:
    Raw Data
    =========
    
    SELECT DISTINCT 
       155 AS sort_number,
       00 AS foreign_key,
       'one_liner' AS field_name,
       'Y' AS is_null
    FROM 
       mishap
    WHERE 
       (mshp_oneliner IS NULL AND mshp_id = plForeignKey)
    
    
    1st string output (with duplicates)
    ===================================
    sort_number; foreign_key; one_liner; field_name; is_null; mshp_oneliner; mshp_id; foreign_key
    
    
    2nd string output (without duplicates)
    ======================================
    sort_number; foreign_key; one_liner; field_name; is_null; mshp_oneliner; mshp_id

    Attached Thumbnails Attached Thumbnails Extraction.JPG  
    Attached Files Attached Files

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Not really sure what you're doing but

    WHERE somefield like "*_*"

  3. #3
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Unless I'm misunderstanding, this is a simple wildcard and will find all three records. Instead, I need to extract the particular keywords.

    Thanks for trying anyways....

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    this function is close to what you want, you will need to finish it off

    Code:
    Function listKeywords(rData As String) As String
    Dim r As String
    Dim a() As String
    Dim i As Integer
    Dim f As String
    
    
    'assumption keywords are preceded with a space and end with a space or other character
    
    'standardise other characters to spaces
    r = Replace(rData, ";", " ")
    r = Replace(r, "!", " ")
    r = Replace(r, ":", " ")
    r = Replace(r, ",", " ")
    r = Replace(r, Chr(10), " ")
    r = Replace(r, Chr(13), " ")
    'etc
    
    
    'remove double spaces
    While InStr(r, "  ") <> 0
         r = Replace(r, "  ", " ")
    Wend
    
    
    'pass to array
    a = Split(r, " ")
    
    
    'loop through array to find keywords
    For i = 0 To UBound(a) - 1
        If InStr(a(i), "_") <> 0 Then 'this is a keyword
            If InStr("; " & f, "; " & a(i)) = 0 Then f = f & "; " & a(i)  'add if not already added
        End If
    Next i
    
    
    listKeywords = Mid(f, 3)
    
    
    End Function
    call it in a query

    SELECT RawData.[Raw Data], listKeywords([raw data]) AS Expr1
    FROM RawData

    these are the results - equivalent to column C
    Expr1
    sort_number; foreign_key; 'first_line_supervisor'; field_name; is_null; pers_employment_status1_id; pers_employment_status2_id; (pers_factor_flag; pers_operator_flag; pers_injured_flag; ((pers_1st_line_supervisor; pers_id; A1st_line; name/AFSASDATA.PERSON.PERS_1ST_LINE_SUPERVISOR
    sort_number; foreign_key; 'one_liner'; field_name; is_null; (mshp_oneliner; mshp_id
    PERS_DAYS_ON_QUARTERS

    you can see the issues - some words have been combined, (e.g. is_nullFROM), some still have other characters such as ( etc, you just need to find all the characters you want to ignore and include in the replace section - but be aware if these characters form part of a key word, you will have a problem

  5. #5
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Ajax -- totally awesome. This is definitely a great starting point. I'll see if I can finish it (e.g., removing parentheses, etc.)... thank you!!!

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    with regards 'isNullFROM' that appears to be a factor of richtext/plaintext. richtext uses html, not char 10/13 so you will probably need to look at how you are populating your table with the sql.

    If I change the text type in the table from plaintext to richtext, save it then change back to plaintext and save again, the problem is solved
    Expr1
    sort_number; foreign_key; 'first_line_supervisor'; field_name; is_null; pers_employment_status1_id; pers_employment_status2_id; (pers_factor_flag; pers_operator_flag; pers_injured_flag; ((pers_1st_line_supervisor; pers_id; A1st_line; name/AFSASDATA.PERSON.PERS_1ST_LINE_SUPERVISOR
    sort_number; foreign_key; 'one_liner'; field_name; is_null; (mshp_oneliner; mshp_id
    PERS_DAYS_ON_QUARTERS

    A solution may be to change the field to rich text and modify this line of the function

    r = Replace(rData, ";", " ")

    to


    r = Replace(plaintext(rData), ";", " ")

  7. #7
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Hmh, not sure if I'm fully tracking... I made the mods to both table (i.e., set it to rich text) and updated the function. However, I still get the "(" or "((" in the expression output. See example below.

    Code:
    sort_number; foreign_key; 'first_line_supervisor'; field_name; is_null; pers_employment_status1_id; pers_employment_status2_id; (pers_factor_flag; pers_operator_flag; pers_injured_flag; ((pers_1st_line_supervisor; pers_id; A1st_line; name/AFSASDATA.PERSON.PERS_1ST_LINE_SUPERVISOR
    Is there a way to strip off the parentheses?

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    suspect you have not added the line to replace (

  9. #9
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Ajax -- yes, you were right. I love how simple your code is designed and it's so easy to add other character delimiters. 1000 thanks!!

    Cheers,
    Tom

  10. #10
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Ajax -- I posted a follow-up question at: https://www.accessforums.net/showthr...584#post471584

  11. #11
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I'm moving house over the next few days so regret don't have the time right now

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Simple table relationships ("faces" to "spaces" to "chairs")
    By skydivetom in forum Database Design
    Replies: 36
    Last Post: 07-20-2019, 01:49 PM
  2. Replies: 5
    Last Post: 06-26-2019, 12:18 PM
  3. Replies: 3
    Last Post: 06-06-2018, 08:26 PM
  4. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  5. Replies: 8
    Last Post: 04-18-2013, 04:41 PM

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