Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jul 2003
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extract text from a cell (ACCESS 2000) (ACCESS 2000)

    HELP!!! I'm trying to find out how I can extract certain text from a many cells via query.
    What I have is a list of manufactures with their part number all in one cell. I would like to extract the PN into a separate cell. I would do this in Excel with the 'MID' and 'FIND' functions, but my list is around 125000 lines and won't fit. The part numbers are separated from the MFG by a space. I have tried the 'SEARCH' function in the expression builder that the Access help says it has, but I get an error indicating that this is an unrecognizable function. Does anyone know of any way (w/out using VB) to extract this information. Thanks in advance for the help.
    Drew Patton

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract text from a cell (ACCESS 2000) (ACCESS 2000)

    SEARCH is an Excel worksheet function; it is not available in Access. You can use the InStr function, in combination with Left, Mid and Right.

    InStr(strText, strSearch) tries to find strSearch in strText and returns the position of the first character where strSearch is found, or 0 if it is not found.

    Example: InStr("Woody's Lounge", "dy") returns 4 because "dy" starts at position 4 in "Woody's Lounge".

    There is a variant of InStr in which you specify where to start looking: InStr(StartPos, strtext, strSearch).

    Example: InStr(8, "Woody's Lounge", "dy") returns 0 because "dy" is not found if you start from the 8th character in "Woody's Lounge".

    Does this give you enough to work with? If not, provide more details (is the structure "MFG PN" or "PN MFG" or something else?)

  3. #3
    New Lounger
    Join Date
    Jul 2003
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract text from a cell (ACCESS 2000) (ACCESS 2000)

    IT WORKS!!!
    Thanks, you help is greatly appreciated!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •