substring

[kristinac]

I am trying to use the SQL substring function. Access doesn't recognize it at all, and I can't get it to work in my vb code either. I need to retrieve the part of a field that comes after the last space. I've tried everything I have found and am obviously not doing it right. What would the syntax be?
I've tried SUBSTRING(StreetTest.ST_NAME, 0, CHARINDEX(' ')), and many variations of this, but I'm getting nowhere...
Help please?

 

[scriverb]

If you are using A2K there is a function called InStrRev which can be used with the Mid$ function to give you what you need:
Dim x as string
x = Mid$(InStrRev(StreetTest.ST_NAME, " &quot+1, StreetTest.ST_NAME)

You see the Mid$ function is used to parse out the substring. The first parameter of the Mid$ function is the left starting point of the substring that you want. Because you want to start at the last space +1 from the right the InStrRev function needs to be used to find this location. It starts at the right and searches to the left a character at a time until it finds the search character which is the second parameter of that function.

Now if you are using Access 97 this InStrRev is not available. But, I have written a Function of my own that can be copied and pasted into a Database module and then used within the Mid$ function to accomplish this task:
Public Function InStrRight(vSearchStr As String, vTargetStr As String, vStart As Long) As Long
Dim i As Integer
For i = vStart To 1 Step -1
If InStr(i, vSearchStr, vTargetStr, 1) = i Then
InStrRight = i 'Position of vTargetStr
Exit For
End If
Next i
End Function

I named this a little different InStrRight so that they would not get confused with the A2K function. It works in a similar way. The first parameter is the string to be searched, the second is the search character, and the third is the starting point in the string. Just a little different because I found it beneficial to be able to start searching to the left from someplace other than the end of the string. So to use this function to accomplish your task in A97 the following code will work:

:
Dim x as string
x = Mid$(InStrRight(StreetTest.ST_NAME, " ", Len(StreetTest.ST_NAME))+1, StreetTest.ST_NAME)

Post back if you have any question concerning this options.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[kristinac]

Thank you very much. That is the function I was trying to use but I wasn't doing it right. Where exactly are you including this code? Within a query or in a pgm that accesses the database?

 

[scriverb]

The code that I provided can be used as the code behind a command button on a form. You can also use the code in a SQL query. In a select query to just show the part being stripped off use the following:
Select A.ST_NAME, Mid$(InStrRight(A.ST_NAME, " ", Len(A.ST_NAME))+1, A.ST_NAME) as Parsed_St_Name
FROM StreetTest as A;

Post back if you see more questions.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[kristinac]

Thanks very much Bob...