Results 1 to 4 of 4
  1. #1
    rkalapura is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    New York
    Posts
    69

    trim data as needed

    Hai guys,
    I have a table with part numbers, text field, 50 character long. Most of the part numbers has two underscores in it, followed by D4*. I would like to extract the part number before the underscores starts. For eg some part numbers are listed below:


    _09375-440C-SP-P11-36P__D46
    _09375-440CVM-SP-GR10-M06-36P__D46
    _09375-440CVM-SP-GR10-M06-36P__D46-DFL
    _09375-CHROME-GR10-M02-36P__D6
    _09375-CHROME-GR10-M04-36P__D46
    _09375-CHROME-GR10-OK-36P__D4
    _09375-CHROME-GR10-P02-36P
    _09375-CHROME-GR10-P04-36P
    I want to extract numbers upto the double underscores; removing __D* from every part number. Numbers should appear like the following:
    _09375-440C-SP-P11-36P
    _09375-440CVM-SP-GR10-M06-36P
    _09375-440CVM-SP-GR10-M06-36P
    _09375-CHROME-GR10-M02-36P
    _09375-CHROME-GR10-M04-36P
    _09375-CHROME-GR10-OK-36P
    _09375-CHROME-GR10-P02-36P
    _09375-CHROME-GR10-P04-36P

    I have almost 10,000 part numbers in my table and every part number start with an underscore. I have done it in excel, but how do I do it in Access 2010 using some functions? This I will used it for a dropdown.

    Thanks for your help.

  2. #2
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    Since the string "__d4" does not always occur in the part_no field, it's a bit complex, but this expression should work:

    iif(instr(part_no,"__d4") = 0, part_no, left(part_no , instr(part_no & "__D4", "__d4") -1 ))

    The reason I put the & "__d4" in the second part is that the IIF evaluates both the True and False of the expression, and if the "__D4" is not in the part_no, the left() function generates an error. If "__D4" is already in the part_no, adding the second bit does not matter, since instr() gives you the first occurence. I gave it a quick test in A2003, and it seems to be fine.

    John

  3. #3
    rkalapura is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    New York
    Posts
    69

    Trim Data as Needed

    Thank you very much John. That was awsome. I just removed 4 from "__D4" and used the expression with "__D" and that trimmed all the D4, D46, D64 and D6 etc. etc.

    Thank you and best regards

    God Bless You!

    Quote Originally Posted by John_G View Post
    Hi -

    Since the string "__d4" does not always occur in the part_no field, it's a bit complex, but this expression should work:

    iif(instr(part_no,"__d4") = 0, part_no, left(part_no , instr(part_no & "__D4", "__d4") -1 ))

    The reason I put the & "__d4" in the second part is that the IIF evaluates both the True and False of the expression, and if the "__D4" is not in the part_no, the left() function generates an error. If "__D4" is already in the part_no, adding the second bit does not matter, since instr() gives you the first occurence. I gave it a quick test in A2003, and it seems to be fine.

    John

  4. #4
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Glad I was able to help -

    All the best.

    John

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

Similar Threads

  1. Replies: 1
    Last Post: 11-21-2011, 01:09 AM
  2. Replies: 4
    Last Post: 08-19-2011, 01:53 PM
  3. Replies: 3
    Last Post: 06-20-2011, 03:09 PM
  4. avg/min/max data help needed badly
    By donavan01 in forum Access
    Replies: 7
    Last Post: 02-13-2011, 12:49 PM
  5. COPY DATA and TRIM
    By Ed H in forum Access
    Replies: 1
    Last Post: 01-01-2009, 07:01 AM

Tags for this Thread

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