Re: How do you use the Instr function to go from right to left?
Originally posted by lansing
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)