Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Looking for a '-9' in a string (A2k2)

    Hello All,
    I am using a form control to look up a part number in a ODBC connected table. The right hand part of the string is "-9" for the part numbers I want returned. The hard part is that there is some spaces at the end of the number. I would like to say that there will always be 4 spaces but I cannot say for sure. So...... Is there a way to strip off the spaces at the end of the string and then look for anything that has the "-9"??? Here is an example of one of the part numbers (four end spaces included):
    050-10029-9

    Thanks,
    Mark

  2. #2
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for a '-9' in a string (A2k2)

    Try:

    SELECT Trim([MyFieldName]) AS XX
    FROM MyTableName
    WHERE ((Right([MyFieldName],2)="-9"))

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

    Re: Looking for a '-9' in a string (A2k2)

    Shouldn't that be WHERE Right(Trim([MyFieldName]),2) = "-9" or, alternatively, WHERE Trim([MyFieldName]) Like "*-9"

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Looking for a '-9' in a string (A2k2)

    I think you will have to change it to:
    SELECT Trim([MyFieldName]) AS XX
    FROM MyTableName
    WHERE ((Right(Trim([MyFieldName]),2)="-9"))
    or
    SELECT Trim([MyFieldName]) AS XX
    FROM MyTableName
    WHERE ((Right(XXX,2)="-9"))

  5. #5
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for a '-9' in a string (A2k2)

    Hans,

    The SQL I posted seemed to work but now I am not so sure. In a Select statement with a Where, in which order is the SQL processed?

    James

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

    Re: Looking for a '-9' in a string (A2k2)

    Whatever the order of execution, in

    SELECT Trim([MyFieldName]) AS XX
    FROM MyTableName
    WHERE ((Right([MyFieldName],2)="-9"))

    the WHERE condition is on MyFieldName, not on the trimmed field XX. You won't notice the difference in a test in Access, since Access automatically trims trailing spaces when you enter data in a text field. But Mark Santos is working with a table in another format, linked via ODBC. Text fields in such a table may well contain trailing spaces.

  7. #7
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for a '-9' in a string (A2k2)

    Thanks. Learned something new today.

Posting Permissions

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