Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    Can Jose, CA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Stripping data from variable length fields (2k)

    I have a variable length field that I want to strip some info from. The data I want ends before an ' (apostrophe) in all cases. ie: 1stWS-C6506' 68-1234- needs strip to equal 1stWS-C6506, WS-X6K-S2-MSFC2' 68-1488 needs to strip to equal WS-X6K-S2-MSFC2. This data is in a query and I would like to create a field expression for this strip function. Anyone know what the expression would be to take/read all data before the ' into a field. A simple field1:=LEFT([abc],x) doesn't work as x is variable. If it can't be done in a query expression, what would the VBA code look like?

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Stripping data from variable length fields (2k)

    Recommend use InStr function to locate position of the apostrophe, then subtract 1:

    <pre>SELECT Table1.Text1, Left([Text1],InStr([Text1],"'")-1) AS Expr1
    FROM Table1;</pre>

    Table1 is name of table, and Text1 is name of field with text to be truncated.

    HTH

  3. #3
    Star Lounger
    Join Date
    Aug 2001
    Location
    Can Jose, CA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stripping data from variable length fields (2k)

    Thanks for the reply. Your suggestion was the answer.

Posting Permissions

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