Results 1 to 4 of 4
  1. #1
    Johnny12 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2012
    Posts
    31

    How to extract from a comment string?

    I have two separate data sources that don't talk to each other and I need to do a comparison of the information. Each system has a primary key but only one is shared, sort of. We have System S and system H. I can do an excel data extract from both. The extract from system H does not have any primary keys from system S. The data extract from system S does have the primary key for system H, but it is within a comment section. And there could be multiple primary keys from system H in the comments of system S. The one good thing, it is always in the exact same format. Bad part, there could also be general comments before or after in the comment. for example. "Estimated delivery is 01/01/2020 12345(5), 23456(45) 34567(6). Follow Up with ACME Corp in 10 days" The 5 digit number is the primary key for system H and the number in the () is the quantity for that order. If I can extract that Primary Key for system H, then I could match that record from system to the records in system H. If I can extract the number and QTY together, I know how to separate into two fields. I just don't know how to go about extracting them from the string. Note, there could be one or many numbers+qty to extract.


    Since it is a 1 to many match, I'm thinking I copy the comments field with the system S ID# to a new table, do the separating then put it back together later.

    So, in field called Comments we have: "Estimated delivery is 01/01/2020 12345(5), 23456(45) 34567(6). Follow Up with ACME Corp in 10 days"

    I need to extract:



    ID = 12345 Qty =5
    ID = 23456 Qty=45
    ID = 34567 Qty=6

    I'm not even sure what to google to figure this out. Any help would be appreciated.

    thanks!

    Johnny

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    because you could have multiple ID's within one statement, you will almost certainly need to use vba code to create a function.

    The actual extraction is fairly straightforward based on your simple example.

    The code would do the following

    find a (
    get the 5 characters before the bracket
    get the numerical value after the (

    using the instr function it might be something like

    ID=mid(myfield,instr(myfield,"(")-6,5)
    Qty=val(mid(myfield,instr(myfield,"(")+1))

    but there are lots of potential issues that need to be handled

    code will fail if there is not a ( in myfield
    code will produce a wrong result if ID's are not always 5 chars
    code will produce a wrong result if a ( is used elsewhere in myfield
    what do you want to do with the outcome? create a new record for each ID? Simply list them? something else?

    So to get a workable solution you need to consider all possible formats for myfield to identify the above potential issues. Other things to consider are do the id/qty always appear grouped as you show, or can then appear in different parts of the text?

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    This won't be simple. Will most likely require VBA. Consistency of structure is critical in string manipulation. How representative is this one example? You show 3 primary keys and quantities with a comma between 2 of them. Will these always be grouped together one after the other?

    Should probably provide a larger sample of data.
    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.

  4. #4
    Johnny12 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2012
    Posts
    31
    Everyone, thank you for the comments. Combined you gave me some ideas and this is what I did to solve the problem. Basically I counted to find the brackets then used Mid and Right to cut away the first string I needed. After reviewing the data, the max was 4 IDs in the string, so basically I ran it, chopped off first ID number, saved it to a new table, updated the original table with the remainder of the string then ran it again until no more are found. Once that was done I just linked the tables and brought it all together.

    Thanks for getting my brain to work!


    Click image for larger version. 

Name:	ExtractingString.jpg 
Views:	8 
Size:	30.0 KB 
ID:	42160

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

Similar Threads

  1. Extract string before and after a period
    By bchi99 in forum Queries
    Replies: 3
    Last Post: 11-03-2014, 04:08 PM
  2. Extract part of string
    By Fais in forum Access
    Replies: 5
    Last Post: 08-06-2014, 04:46 PM
  3. Extract a number from a string
    By webisti in forum Access
    Replies: 3
    Last Post: 09-16-2013, 08:29 AM
  4. Extract Value from Variable in String
    By nguyenak in forum Programming
    Replies: 3
    Last Post: 05-24-2012, 03:50 PM
  5. Extract String From Between 2 Values
    By kathleencampbell in forum Queries
    Replies: 5
    Last Post: 03-23-2012, 10:52 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