I am trying to find the position of the second space in a string, but have not been able to find a solution. Any suggestions on how I could do this using an expression in the QBE window or VBA would be appreciated. Thanks in advance.
You can nest as many InStr functions as needed (AFAIK: I've done ten below) but with a rather severe constraint: The string you're searching must contain at least as many search characters as the number of nested InStr functions.
IOW, If you need the 3rd space in a string, there must be at least 3 spaces in the string or 3 nested InStr functions will likely return the wrong character position.
With just the 2 nested InStr calls, there is no such constraint.
That being said, there is a method of nesting InStr functions that will work no matter how many search characters there are in the string to be searched, but it is unwieldy and very non-intuitive. Below are 10 nested InStr function calls that return the character position of the 10th comma found in ttext, or 0 if there is no 10th comma:
This works by insuring there are 10 search string characters (in this case, commas) in any string to be searched for each nested InStr function call. Since a string of 10 commas is concatenated at the end of the string to be searched, any 10th comma found in a character position greater than the length of the original string was not in the original string and the function will return 0.
Much easier to design a custom function that can be repeatedly called for any position of the space character:
Code:
Function fncSpPos(strPassedString As String, iNum As Integer) As Integer
fncSpPos = 0
While Len(strPassedString) And (iNum > 0)
If InStr(1, strPassedString, " ") Then
fncSpPos = fncSpPos + InStr(1, strPassedString, " ")
strPassedString = Mid(strPassedString, InStr(1, strPassedString, " ") + 1)
iNum = iNum - 1
Else
fncSpPos = 0
Exit Function
End If
Wend
If iNum Then fncSpPos = 0
End Function
Call the function in your query like so:
3rdSpaceAt: fncSpPos([ttext],3)
where ttext is the string to be searched and 3 is the "3"rd space.
Here's a UDF to play with. It'll return the number of occurences and the location of each occurence.
Code:
Function StrCount2(ByVal TheStr As String, theItem As Variant) As String
'------------------------------------------------------------------
' Purpose: Return number and location(s) of item occuring in a string.
' Coded by: raskew
' Arguments: TheStr: The string to be searched.
' TheItem: The item to search for.
' To test: Type ? StrCount2("The quick brown fox jumped over
' the lazy dog", " ") in the debug (immediate) window.
' Returns: 8: 4, 10, 16, 20, 27, 32, 36, 41
'------------------------------------------------------------------
Dim j As Integer
Dim inthold As Integer
Dim placehold As Integer
Dim strHold As String
Dim strKeep As String
Dim itemhold As Variant
strHold = TheStr
itemhold = theItem
j = 0
If InStr(1, strHold, itemhold) > 0 Then
While InStr(1, strHold, itemhold) > 0
placehold = InStr(1, strHold, itemhold)
inthold = inthold + placehold
strKeep = strKeep & inthold & ", "
j = j + 1
strHold = Mid(strHold, placehold + Len(itemhold))
Wend
StrCount2 = j & ": " & Left(Trim(strKeep), Len(Trim(strKeep)) - 1)
End If
End Function
Thanks for each of your responses. I went ahead and used DALeffler's InStr(InStr(InStr(1,[ttext]," ")+1,[ttext]," ")+1,[ttext]," ") for this instance, but will keep the other examples in mind for future reference.
I have another question concerning extracting substrings. I need to find the first set of parentheses in a string and extract the last word between them.
Example;
(200 mcg) I need to parse out the mcg (could be mg, unit, meq, gram, etc.) So, I would need to find the first space between the parantheses and grab the word after the space.
I was bored so heres a 10 minute snapshot of my life.
This is a modified take of DAleffler's fncSpPos, I borrowed raskew's header format for clarity...
Code:
Private Function IndexOfNth(ByVal stringToSearch As String, ByVal nthOccurence As Integer, ByVal stringToFind As String) As Integer
'------------------------------------------------------------------
' Purpose: Return a zero-based index of the starting position of one string within
' another string given its nth occurence within that string.
' Coded by: wbrumbalow
' Arguments: stringToSearch: The string to be searched.
' nthOccurence: The nth occurence of stringToFind within stringToSearch.
' stringToFind: The string to find.
' To test: Paste ' ? IndexOfNth("The quick brown fox", 3, " ") ' in the debug (immediate) window.
' Returns: 15
' Note: -1 is returned if the nthOccurence of stringToFind does not exist
'------------------------------------------------------------------
IndexOfNth = -1
While Len(stringToSearch) And (nthOccurence > 0)
If InStr(1, stringToSearch, stringToFind) Then
IndexOfNth = IndexOfNth + InStr(1, stringToSearch, stringToFind)
stringToSearch = Mid(stringToSearch, InStr(1, stringToSearch, stringToFind) + 1)
nthOccurence = nthOccurence - 1
Else
IndexOfNth = -1
Exit Function
End If
End While
If nthOccurence = 1 Then
IndexOfNth = -1
End If
End Function
Hi everybody. Ths is my first contribution to this community. Now I'm retired and have more time to share my experiences as an Access Vba programmer.
Using the follwing code in a simple query, you can get the position of both the first and second blank spaces, and also a message is rthere are no spaces in the text, or less then 2 spaces.
SELECT YourField, InStr([YourField]," ") AS [First],
Mid([YourField],[first]+1) AS wTxt,
InStr([wTxt]," ") AS [Second],
IIf([First]>0 And [Second]>0,[First]+[Second],IIf([First]=0 And [Second]=0,"no spaces",IIf([Second]=0,"only 1 space"))) AS wP
FROM YourTable;
