Trying to find text within a text string

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Faz
    #1

    Trying to find text within a text string

    I am trying to extract text before a certain character appears in a
    string. This character is the letter "C".

    Here is some sample data - the field is called REFERENCE_2:

    REFERENCE_2
    10299C17264
    9841C17218

    I want to extract 10299 for the first line and 9841 for the second
    line into a SQL query. I am trying to use the FIND function so I can
    get the position of C and do a MID function on this position from the
    first character to the position of C - 1, but FIND does not work in
    the query builer screen.

    I am not proficient with VB and want to use the normal query
    functionality.

    Many thanks.
    Tags: None
    • PC Datasheet
      #2
      Re: Trying to find text within a text string

      MyNumber = Left([Reference_2],Instr([Reference_2],"C")-1)


      --
      PC Datasheet
      Your Resource For Help With Access, Excel And Word Applications
      resource@pcdata sheet.com
      https://www.pcdatasheet.com



      "Faz" <afazeel@hotmai l.com> wrote in message
      news:2663ec5.04 07051842.97254d 6@posting.googl e.com...[color=blue]
      > I am trying to extract text before a certain character appears in a
      > string. This character is the letter "C".
      >
      > Here is some sample data - the field is called REFERENCE_2:
      >
      > REFERENCE_2
      > 10299C17264
      > 9841C17218
      >
      > I want to extract 10299 for the first line and 9841 for the second
      > line into a SQL query. I am trying to use the FIND function so I can
      > get the position of C and do a MID function on this position from the
      > first character to the position of C - 1, but FIND does not work in
      > the query builer screen.
      >
      > I am not proficient with VB and want to use the normal query
      > functionality.
      >
      > Many thanks.[/color]


        Comment

        • almish
          #3
          Re: Trying to find text within a text string

          afazeel@hotmail .com (Faz) wrote in message news:<2663ec5.0 407051842.97254 d6@posting.goog le.com>...[color=blue]
          > I am trying to extract text before a certain character appears in a
          > string. This character is the letter "C".
          >
          > Here is some sample data - the field is called REFERENCE_2:
          >
          > REFERENCE_2
          > 10299C17264
          > 9841C17218
          >
          > I want to extract 10299 for the first line and 9841 for the second
          > line into a SQL query. I am trying to use the FIND function so I can
          > get the position of C and do a MID function on this position from the
          > first character to the position of C - 1, but FIND does not work in
          > the query builer screen.[/color]

          I believe that Find() is an Excel function - in Access VBA you should use
          the InStr() function to retrieve the position of another substring (in
          Access 2000 and newer).

          Something like this in a query's Field row:

          FirstPart: Left$([REFERENCE_2], InStr([REFERENCE_2], "C") - 1)

          assuming the REFERENCE_2 never has a Null value - otherwise you'll need to
          use the Nz() function or the IIF() function to handle that possibility.

          - John Mishefske

            Comment

            Previous template Next
            Working...