Query - Search STR for second occurance

[MVP - WannaB]

Hi, I am trying to remember how to search text field for the second
occurrence of a string, but something is not working for me. If anyone can
help I would greatly appreciate it.
I am creating a calculated field in a query to verify the results of each
piece before I put everything together to pull 7 different strings from this
one text field. Here is what I have so far and it comes back as the same
starting place as the first occurrence>> BPS2:
InStr(InStr(1,[t_transactions]![pricing]," ",1),[t_transactions]![pricing],"
",1)
I expected that would find the second occurrence of double spaces, I would
then need to subtract from the initial location to acquire the length of the
string I need. As I said I need to do this 7 different time to pull data out
of this one text field and parse it out to new fields.

I also seem to remember using a function some time ago that accepted a param
allowing you to select which occurrence of a string you wanted to retrieve.
Is there such a function in Access ??

 

[John Spencer]

You need to add the length of the search string onto the starting point.

InStr( InStr(1,[t_transactions]![pricing]," ",1)+2,
[t_transactions]![pricing]," ",1)


On the other hand you might want to use the following function to get the 1st,
2nd, 3rd, etc. phrase or word.

For instance to get the text between the 2nd and 3rd occurence of two spaces
you would use

Field: getSection([t_transactions]," ",2)


Public Function getSection(strIn, _
Optional strDelimiter As String = ";", _
Optional intSectionNumber As Integer = 1)
'==============================================================
' Procedure : getSection
' Author : John Spencer
' Purpose : Return section of string
' Access 2000 and later
'==============================================================

Dim strArray As Variant

If Len(strIn & vbNullString) = 0 Then
getSection = strIn
Else
strArray = Split(strIn, strDelimiter, -1, vbTextCompare)

If UBound(strArray) >= intSectionNumber - 1 Then
getSection = strArray(intSectionNumber - 1)
Else
getSection = Null
End If

End If

End Function

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

 

[MVP - WannaB]

I think I have figured it out: I created another field BPS3: InStr(15,"
",1),[t_transactions]![pricing]," ",1) and got the same response >15<, So I
am figuring that I need to add 2 (the number of character spaces FOUND) SO I
added 2 to the function >>BPS2: InStr(InStr(1,[t_transactions]![pricing],"
",1)+2,[t_transactions]![pricing]," ",1) and now I get the number I was
looking for. So my first question has bee resolved.
The other question would just make this whole thing simpler. >>>
IS there a function that will locate each occurance of a string in the field?
Thanks for your time.

 

[KARL DEWEY]

Try this --
InStr(InStr([t_transactions].[pricing]," ")+1, [t_transactions].[pricing],"
")