InStr Function

  • Thread starter Thread starter NEWER USER
  • Start date Start date
N

NEWER USER

I am trying to extract a portion of a string

Here is my string(s) I am playing with

165 E Oregon Ave, Creswell, OR 97426
1517 Bayshore Drive, Coos Bay, OR 97420
248 North 9th Street, Cottage Grove, OR 97424


I used the following to extract the address portion only and it works well.

Mid([F10],1,InStr([F10]," ")-1)

I now want to extract only the City. Can anybody help me? Any help
appreciated.
 
what verion of access do you have if you have a version later than 97
then you can use instrrev if not then you can build a module with it

here is the code for a module for instrrev

'----- start of code -----
Public Function InStrRev( _
StringCheck As String, _
StringMatch As String, _
Optional Start As Long = -1, _
Optional Compare As Integer = 2) _
As Long
'-----------------------------------------------------------
' Inputs: String to check,
' match string,
' optional starting position (default = -1),
' optional string compare value (default vbDatabaseCompare)
' Outputs: Position of match string, starting from the end
' Original code by: John L. Viescas 15-Nov-2001
' Revised by: Dirk Goldgar 21-Jan-2002
' Last Revision: Dirk Goldgar 21-Jan-2002
' ** Duplicates the functionality of the VB 6 INSTRREV function.
'-----------------------------------------------------------
Dim lngS As Long, lngI As Long
Dim lngLenC As Long, lngLenM As Long

' Do some initial checks
If (Compare < 0) Or (Compare > 2) Then
Err.Raise 5
Exit Function
End If
If Len(StringCheck) = 0 Then
InStrRev = 0
Exit Function
End If
If Len(StringMatch) = 0 Then
InStrRev = Start
Exit Function
End If
If Start > Len(StringCheck) Then
InStrRev = 0
Exit Function
End If
If Len(StringMatch) > Len(StringCheck) Then
InStrRev = 0
Exit Function
End If

' OK, have some work to do!
lngS = Start
lngLenC = Len(StringCheck)
lngLenM = Len(StringMatch)
If lngS = -1 Then lngS = lngLenC
lngS = (lngS - lngLenM) + 1
' Set default not found
InStrRev = 0
' Now loop to see if we can find it
For lngI = lngS To 1 Step -1
If StrComp(Mid$(StringCheck, lngI, lngLenM), _
StringMatch, Compare) = 0 _
Then
InStrRev = lngI
Exit For
End If
Next lngI

End Function
'----- end of code -----

the you can use

Mid([F10],InStr([F10],",")+2,InStrrev([F10],",")-1)

that should work as i said if you haev access 97 then you need the
above code otherwise you dont need it

Regards
Kelvan
 
If the strings are always separated into three components by commas and you
are using a version of Access later than 97. You can use a simple vba function
that looks something like the following UNTESTED VBA function.

Public Function fGetToken(strIn, _
Optional strDelimiter as String = " ", _
Optional LPos as long =1)
Dim strArr as Variant

If Len(StrIn & "") = 0 then
fGetToken = strIN
ELSE
strArr = Split(StrIn,strDelimiter)
If UBound(strArr) < LPos then
fGetToken = strArr(LPos-1)
Else
fGetToken = Null
End if
END IF

End Function

In a query, you would just use
Field: fGetToken([Address Field],",",2)

Or if the separator was comma space
Field: fGetToken([Address Field],", ",2)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
I have Access 2003; I tried your coded
Mid([F10],InStr([F10],",")+2,InStrrev([F10],",")-1)

It returns the City, State and zip - Creswell, OR 97426

I need the CITY only
 
I should have tested that function. My comparison was wrong. This was has
been minimally tested and seems to work.

Public Function fGetToken(strIn, _
Optional strDelimiter As String = " ", _
Optional LPos As Long = 1)
Dim strArr As Variant

If Len(strIn & "") = 0 Then
fGetToken = strIn
Else
strArr = Split(strIn, strDelimiter)
If LPos - 1 <= UBound(strArr) Then
fGetToken = strArr(LPos - 1)
Else
fGetToken = Null
End If
End If

End Function

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

John said:
If the strings are always separated into three components by commas and
you are using a version of Access later than 97. You can use a simple
vba function that looks something like the following UNTESTED VBA function.

Public Function fGetToken(strIn, _
Optional strDelimiter as String = " ", _
Optional LPos as long =1)
Dim strArr as Variant

If Len(StrIn & "") = 0 then
fGetToken = strIN
ELSE
strArr = Split(StrIn,strDelimiter)
If UBound(strArr) < LPos then
fGetToken = strArr(LPos-1)
Else
fGetToken = Null
End if
END IF

End Function

In a query, you would just use
Field: fGetToken([Address Field],",",2)

Or if the separator was comma space
Field: fGetToken([Address Field],", ",2)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

NEWER said:
I am trying to extract a portion of a string

Here is my string(s) I am playing with

165 E Oregon Ave, Creswell, OR 97426
1517 Bayshore Drive, Coos Bay, OR 97420
248 North 9th Street, Cottage Grove, OR 97424


I used the following to extract the address portion only and it works
well.

Mid([F10],1,InStr([F10]," ")-1)

I now want to extract only the City. Can anybody help me? Any help
appreciated.
Click to expand...
Click to expand...
 
You MVP's absolutely amaze me with your wealth of knowledge. It worked
perfect. If I wanted to extract the STATE by itself, where would I change
the code from your previous reply. I appreciate your time. Thanks again.

John Spencer said:
I should have tested that function. My comparison was wrong. This was has
been minimally tested and seems to work.

Public Function fGetToken(strIn, _
Optional strDelimiter As String = " ", _
Optional LPos As Long = 1)
Dim strArr As Variant

If Len(strIn & "") = 0 Then
fGetToken = strIn
Else
strArr = Split(strIn, strDelimiter)
If LPos - 1 <= UBound(strArr) Then
fGetToken = strArr(LPos - 1)
Else
fGetToken = Null
End If
End If

End Function

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

John said:
If the strings are always separated into three components by commas and
you are using a version of Access later than 97. You can use a simple
vba function that looks something like the following UNTESTED VBA function.

Public Function fGetToken(strIn, _
Optional strDelimiter as String = " ", _
Optional LPos as long =1)
Dim strArr as Variant

If Len(StrIn & "") = 0 then
fGetToken = strIN
ELSE
strArr = Split(StrIn,strDelimiter)
If UBound(strArr) < LPos then
fGetToken = strArr(LPos-1)
Else
fGetToken = Null
End if
END IF

End Function

In a query, you would just use
Field: fGetToken([Address Field],",",2)

Or if the separator was comma space
Field: fGetToken([Address Field],", ",2)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

NEWER said:
I am trying to extract a portion of a string

Here is my string(s) I am playing with

165 E Oregon Ave, Creswell, OR 97426
1517 Bayshore Drive, Coos Bay, OR 97420
248 North 9th Street, Cottage Grove, OR 97424


I used the following to extract the address portion only and it works
well.

Mid([F10],1,InStr([F10]," ")-1)

I now want to extract only the City. Can anybody help me? Any help
appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Well, since the state should be the first two characters of the third token I
would probably try the following.

Left(Trim(fGetToken([F10],3)),2)

And to get the zip code, I would try
Mid(Trim(fGetToken([F10],3)),3)

You might not need the TRIM part. Experiment and see what your results are.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
The two suggestions gave me wrong results

I got the first two characters in the string (numbers in the address) and
not the first two characters of the third token for the State code. The zip
code gave me the whole string starting at the third character (numbers in the
address) in the string.

John Spencer said:
Well, since the state should be the first two characters of the third token I
would probably try the following.

Left(Trim(fGetToken([F10],3)),2)

And to get the zip code, I would try
Mid(Trim(fGetToken([F10],3)),3)

You might not need the TRIM part. Experiment and see what your results are.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

NEWER said:
You MVP's absolutely amaze me with your wealth of knowledge. It worked
perfect. If I wanted to extract the STATE by itself, where would I change
the code from your previous reply. I appreciate your time. Thanks again.
Click to expand...
Click to expand...
 
My bad, I missed an argument in the fGetToken call.

fGetToken([F10],", ",3)

See if you can combine that with my earlier suggestion to get what you want.

By the way, when something doesn't work it is a good technique to break it
down into its parts so you can see where things have gone wrong.



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

NEWER said:
The two suggestions gave me wrong results

I got the first two characters in the string (numbers in the address) and
not the first two characters of the third token for the State code. The zip
code gave me the whole string starting at the third character (numbers in the
address) in the string.

John Spencer said:
Well, since the state should be the first two characters of the third token I
would probably try the following.

Left(Trim(fGetToken([F10],3)),2)

And to get the zip code, I would try
Mid(Trim(fGetToken([F10],3)),3)

You might not need the TRIM part. Experiment and see what your results are.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

NEWER said:
You MVP's absolutely amaze me with your wealth of knowledge. It worked
perfect. If I wanted to extract the STATE by itself, where would I change
the code from your previous reply. I appreciate your time. Thanks again.

:

I should have tested that function. My comparison was wrong. This was has
been minimally tested and seems to work.

Public Function fGetToken(strIn, _
Optional strDelimiter As String = " ", _
Optional LPos As Long = 1)
Dim strArr As Variant

If Len(strIn & "") = 0 Then
fGetToken = strIn
Else
strArr = Split(strIn, strDelimiter)
If LPos - 1 <= UBound(strArr) Then
fGetToken = strArr(LPos - 1)
Else
fGetToken = Null
End If
End If

End Function

John Spencer
Access MVP 2002-2005, 2007-2008
Click to expand...
Click to expand...
Click to expand...
 
I removed the Mid and Left Functions and added TRIM
Trim(fGetToken([F10],", ",3))
All is working well. Thanks for getting me on track. Truly appreciated.


John Spencer said:
My bad, I missed an argument in the fGetToken call.

fGetToken([F10],", ",3)

See if you can combine that with my earlier suggestion to get what you want.

By the way, when something doesn't work it is a good technique to break it
down into its parts so you can see where things have gone wrong.



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

NEWER said:
The two suggestions gave me wrong results

I got the first two characters in the string (numbers in the address) and
not the first two characters of the third token for the State code. The zip
code gave me the whole string starting at the third character (numbers in the
address) in the string.

John Spencer said:
Well, since the state should be the first two characters of the third token I
would probably try the following.

Left(Trim(fGetToken([F10],3)),2)

And to get the zip code, I would try
Mid(Trim(fGetToken([F10],3)),3)

You might not need the TRIM part. Experiment and see what your results are.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

NEWER USER wrote:
You MVP's absolutely amaze me with your wealth of knowledge. It worked
perfect. If I wanted to extract the STATE by itself, where would I change
the code from your previous reply. I appreciate your time. Thanks again.

:

I should have tested that function. My comparison was wrong. This was has
been minimally tested and seems to work.

Public Function fGetToken(strIn, _
Optional strDelimiter As String = " ", _
Optional LPos As Long = 1)
Dim strArr As Variant

If Len(strIn & "") = 0 Then
fGetToken = strIn
Else
strArr = Split(strIn, strDelimiter)
If LPos - 1 <= UBound(strArr) Then
fGetToken = strArr(LPos - 1)
Else
fGetToken = Null
End If
End If

End Function

John Spencer
Access MVP 2002-2005, 2007-2008
Click to expand...
Click to expand...
Click to expand...
 
Back
Top