Results 1 to 8 of 8
  1. #1
    jdubp is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2008
    Posts
    15

    How can I delete only a portion of text in a field- that portion that is in parens


    Views: Size: ">Attachment 23489
    I am importing an inventory file from QuickBooks and in the field [ITEM} the QB table has combined our "ITEM" (product part number) and "item Description" into one field.
    Item Description is in parenthesis in all records- text both outside and inside the parens may be different in each record- but every record has open and close parens.
    A space always separates the first text and the text inside the ( ).


    EXAMPLE OF A RECORD: [ITEM] field CONTAINS: A610 (Widgetsgalore) --- where "A610"is our Item Number and (Widgetsgalore) is the Item Description---- separated by a space..

    For all records, I want to delete the parens and all text inside (in example (Widgetsgalore) .... so I end up with a field that has the "item" but not what was in parens.


    "Item" can be 2 to 16 characters long.

    I have included an accdb with one table to show what I mean.

    I have been struggling with MID function and INSTR -- to no avail.

    Any help is appreciated.

    Thanks

    JP
    Attached Files Attached Files
    Last edited by jdubp; 01-21-2016 at 05:09 PM.

  2. #2
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    You could do this with an update query. Look at this link on how to manipulate data using update queries.

    http://www.datapigtechnologies.com/f...anipulate.html

  3. #3
    jdubp is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2008
    Posts
    15
    alansidman: great video thanks. BUT does not show how to update the field to only include portion of the field before the space (or another description would be the portion of the field that is not in parens)

  4. #4
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    use the Mid function and Instr function to locate the "(" and ")" in your feild. Then run an update query.

  5. #5
    jdubp is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2008
    Posts
    15
    I have attached a sample accdb with a Query 1 where I have successfully queried out the information inside the parens, but have not been able to extract the portion of the record outside of the parens (or in front of the "space (" ) and that is what I really need... as it is the inventory part number. (I unsuccessfully tried to use REPLACE to replace " (*)" with "" which would have left just the portion of the field I need.

    Guess I am just not understanding this, so any help is appreciated. .

    Thanks,

    JP

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Create a new query and paste this SQL. See if "Item2A" is what you want
    Code:
    SELECT SOLD.ITEM, SOLD.Qty, (Mid([SOLD]![ITEM],InStr([SOLD]![ITEM]," (")+1)) AS ITEM2, Left([item],InStr([ITEM]," ")) AS Item2A
    FROM SOLD;
    Be aware that "Item" is a reserved word in Access and shouldn't be used for object names.

  7. #7
    jdubp is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2008
    Posts
    15
    Steve: exactly what I needed.
    Thanks,

    JP

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Happy to help..

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

Similar Threads

  1. Replies: 7
    Last Post: 06-09-2014, 04:48 PM
  2. Replies: 7
    Last Post: 06-06-2013, 10:32 AM
  3. Replies: 1
    Last Post: 06-12-2012, 01:54 PM
  4. Bold portion of text in memo field.
    By kenton.l.sparks@gmail.com in forum Access
    Replies: 1
    Last Post: 05-01-2012, 03:32 PM
  5. Replies: 1
    Last Post: 06-09-2006, 05:44 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