Results 1 to 4 of 4
  1. #1
    AccessNubie is offline Novice
    Windows XP Access 2003
    Join Date
    Nov 2009
    Posts
    4

    Talking Extract text from middle of a field

    I want to create a query that pulls the 6 characters to the right of the # sign in a column. There is text before and after this string, but the number of characters vary in each field. below are a few samples i created to give you the idea:



    wero ojkj shipper #879123 sdkfs dlf
    this is shipper #888222 sldkf
    sldf ksd f shipper #123456 testing this
    hello shipper #555888 this is a tst

  2. #2
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    YourString = Mid(YourString,InStr(YourString,"#")+1,6)

    will do the trick


    David

  3. #3
    AccessNubie is offline Novice
    Windows XP Access 2003
    Join Date
    Nov 2009
    Posts
    4
    Ok, i am a SERIOUS access nubie. don't know what to do with this string. The field name is Description. I tried replacing the text YourString in the *query* with the field name Description. No go. Tried Mid([description],InStr([description],"#")+1,6). No good.
    Can you break it down for an ABSOLUTE beginner? Can I do this in a query? Thank you!

  4. #4
    CGM3 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Atlanta, GA
    Posts
    49
    If you haven't already created a query, go to the Queries tab of MS Access and click on the New option; I suggest selecting the Design View, then choose the table with the Description field and click Add, then Close.

    In the first Field column, choose Description from the dropdown list.

    In the second Field column, type--

    Code:
    Mid(Description,InStr(Description,"#")+1,6)
    --which Access will change (after you've pressed the Enter key) to--

    Code:
     
    Expr1: Mid([Description],InStr([Description],"#")+1,6)
    (You can go back and change Expr1 to something like CustomerID if you wish.)

    Click the View icon to "run" the query, which will give you a list of the Description values and the CustomerID from each one.

    Save the query.

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

Similar Threads

  1. split text field
    By Zukster in forum Queries
    Replies: 4
    Last Post: 01-11-2011, 10:01 PM
  2. Split text field into two text fields
    By Grant in forum Access
    Replies: 6
    Last Post: 01-31-2008, 05:52 AM
  3. Extract numbers from text string strored in a field.
    By khabdullah in forum Programming
    Replies: 2
    Last Post: 12-23-2007, 06:55 PM
  4. Quick way to stuff field names into text field
    By kfinpgh in forum Programming
    Replies: 0
    Last Post: 01-04-2007, 01:13 PM
  5. How to extract names from 1 large field
    By Tepin in forum Access
    Replies: 1
    Last Post: 12-04-2006, 01:14 PM

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