Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003
    Posts
    280

    Unanswered: How do you use the Instr function to go from right to left?

    For example:
    Salary <---column name or field
    PT Salary 42,000 <---data
    OT Partime 2,000 <---data

    This is all in one column. I just need to retrieve the value in the column.

    Like, I can do a code that can go from left to right. Instr([Salary], " "), which will give me a value of 3.
    Last edited by lansing; 08-20-03 at 14:11.

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: How do you use the Instr function to go from right to left?

    Originally posted by lansing
    For example:
    Salary <---column name or field
    PT Salary 42,000 <---data
    OT Partime 2,000 <---data

    This is all in one column. I just need to retrieve the value in the column.

    Like, I can do a code that can go from left to right. Instr([Salary], " "), which will give me a value of 3.
    You need the third "word"? Here is a solution, that only works, if your Salary has always two spaces. If not, consider to use my code in a VBA function with more error handlings. Be aware, however, that such a function can only be used within Access, and not by a client application.

    1) Exclude the first word:
    Let x = mid(Salary, Instr([Salary], " ") + 1)

    2) Exclude the second word:
    Let y = mid(x, Instr(x, " ") + 1)

    Together:
    mid(mid(Salary, Instr([Salary], " ") + 1) ,
    Instr(mid(Salary, Instr([Salary], " ") + 1) , " ") + 1)
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

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