Results 1 to 2 of 2
  1. #1
    Dormie is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2014
    Posts
    39

    Query Seperate Column by Keyword

    Hi,
    I have a column of text that I would like to split up by a couple of keywords. I've been trying to mess around with it using iif statements but I don't think that'll do it all. I have little experience with VBA and wouldn't know where to start.

    This is what the column looks like.

    |Fancy Navy Blue Polo|
    |Solid Red Tee Shirt|
    |Fancy Green Jacket|

    I was hoping to split it up to look like this.

    Fancy|Navy Blue|Polo
    Solid|Red|Tee Shirt
    Fancy|Green|Jacket

    I was able to split the first word and last word in the column using excel. But, then I realized some were two words "Tee Shirt". Which made me think maybe I could use some IIf statements but then again realized how would I keep the "Navy Blue" (Center) in the column. I thought maybe if I could separate it with keywords? Or create a column that OMITS keywords?

    Sorry, I'm kind of all over the place..... Maybe someone would have a suggestion or point me in a direction that could help.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    This is why comma is popular choice for separating data elements in a string. Also why consistency in structure is critical in string manipulation. What you want won't be simple, if even possible. A keyword list might be helpful but again, consistency is important. Consider:

    table: Strings
    ID Description
    1 Fancy Navy Blue Polo
    2 Solid Red Tee Shirt
    3 Fancy Green Jacket

    table: KeyWords
    ID KeyWord FieldPosNum
    1 Fancy 1
    2 Navy Blue 2
    3 Solid 1
    4 Polo 3
    5 Red 2
    6 Tee Shirt 3
    7 Green 2
    8 Jacket 3

    query:
    SELECT ID, Description, DLookUp("KeyWord","KeyWords","InStr('" & [Description] & "', [KeyWord])>0 AND [FieldPosNum]=1") AS Word1, DLookUp("KeyWord","KeyWords","InStr('" & [Description] & "', [KeyWord])>0 AND [FieldPosNum]=2") AS Word2, DLookUp("KeyWord","KeyWords","InStr('" & [Description] & "', [KeyWord])>0 AND [FieldPosNum]=3") AS Word3
    FROM Strings;
    ID Description Word1 Word2 Word3
    1 Fancy Navy Blue Polo Fancy Navy Blue Polo
    2 Solid Red Tee Shirt Sold Red Tee Shirt
    3 Fancy Green Jacket Fancy Green Jacket

    Of course this gets blown all to blazes if a keyword can be located in any field position in the Description string.
    Last edited by June7; 08-13-2017 at 02:30 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Looking at values in 2 seperate fields in a query
    By ecalvert47462 in forum Access
    Replies: 3
    Last Post: 09-03-2015, 08:42 AM
  2. Replies: 4
    Last Post: 09-08-2014, 10:06 AM
  3. Sum of Two Feilds of Two Queries in Seperate Query
    By shekar_genius in forum Queries
    Replies: 3
    Last Post: 03-13-2013, 08:27 AM
  4. query multiple tables for keyword
    By BF15 in forum Queries
    Replies: 1
    Last Post: 01-29-2012, 05:18 PM
  5. split a column into two seperate columns
    By nybanshee in forum Access
    Replies: 2
    Last Post: 08-14-2008, 04:52 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