If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Requested string function
I received a request for this function, which I had offered to provide in an
earlier post here. Rather than send it just to the person who emailed me, I'm providing it here. Call the function with 3 parameters, none are optional. The first is a string to be searched. The second is a string (often just one character, or a CrLf) to search for, which I think of as the parse delimiter. The third is an integer showing which "substring" to return. Everything from the start of the string being searched, up to the string that parses it, is the first parsing. Starting after that first occurance of the delimit string is the second parsing, ending at the next occurance of the delimit string or the end of the string. The function is case sensitive. For example: "Now is the time for all good men to come to the aid of their country" Parse this using "i" 1 -5 Now 2 -4 s the t 3 -3 me for all good men to come to the a 4 -2 d of the 5 -1 r country What I've shown is the 5 substrings of the phrase being parsed, broken at each occurrance of "i". They are numbered to the left from 1 to 5. If your 3rd parameter is 1 to 5 you'll get the string shown. The funcion will also parse from the end of the string, using a negative value for the 3rd parameter Occurrance. So, -1 is the last occurrance, -2 is the next to last, and so on. It returns an empty string if the occurrance of the delimited string does not exist. Public Function Parse1(ByVal ObjectString As String, ByVal DelimitString As String, ByVal Occurrance As Integer) As String Dim Count As Integer, Begin As Integer, Found As Integer, DelimitLength As Integer DelimitLength = Len(DelimitString) Parse1 = "" If Occurrance 0 Then Count = 1 Begin = 1 Do Found = InStr(Begin, ObjectString, DelimitString) If Found = 0 Then Exit Do Begin = Found + DelimitLength Count = Count + 1 Loop Occurrance = Count + Occurrance + 1 End If Count = 1 Begin = 1 Do While Count Occurrance Found = InStr(Begin, ObjectString, DelimitString) If Found = 0 Then Exit Function Begin = Found + DelimitLength Count = Count + 1 Loop Found = InStr(Begin, ObjectString, DelimitString) If Found = 0 Then Found = Len(ObjectString) + 1 Parse1 = Mid(ObjectString, Begin, Found - Begin) End Function Enjoy!!! (this line is not part of the code!) Tom Ellison |
#2
|
|||
|
|||
Requested string function
Hi Tom,
I don't think that all these results are what you intendedg: ?parse1("1,2,3,4", ",", 0) 1 ?parse1("1,2,3,4", ",", 1) 1 ?parse1("1,2,3,4", ",", -4) 1 ?parse1("1,2,3,4", ",", -5) 1 Here's my attempt: Function Parse2( _ ByVal V As Variant, _ ByVal Delimiter As String, _ ByVal Index As Long) As Variant 'Treats V as a string containing a list of items separated by Delimiter. 'Returns the Indexth item (first item is index 0). 'Negative index counts from end of list (last item is index -1) 'Returns Null if Index points to an item that's not in the list. Dim arItems As Variant arItems = Split(CStr(V), Delimiter) If Index = 0 Then If Index = UBound(arItems) Then Parse2 = arItems(Index) Else Parse2 = Null End If Else If Abs(Index) = UBound(arItems) + 1 Then Parse2 = arItems(UBound(arItems) + Index + 1) Else Parse2 = Null End If End If End Function On Wed, 14 Dec 2005 17:50:31 -0600, "Tom Ellison" wrote: I received a request for this function, which I had offered to provide in an earlier post here. Rather than send it just to the person who emailed me, I'm providing it here. Call the function with 3 parameters, none are optional. The first is a string to be searched. The second is a string (often just one character, or a CrLf) to search for, which I think of as the parse delimiter. The third is an integer showing which "substring" to return. Everything from the start of the string being searched, up to the string that parses it, is the first parsing. Starting after that first occurance of the delimit string is the second parsing, ending at the next occurance of the delimit string or the end of the string. The function is case sensitive. For example: "Now is the time for all good men to come to the aid of their country" Parse this using "i" 1 -5 Now 2 -4 s the t 3 -3 me for all good men to come to the a 4 -2 d of the 5 -1 r country What I've shown is the 5 substrings of the phrase being parsed, broken at each occurrance of "i". They are numbered to the left from 1 to 5. If your 3rd parameter is 1 to 5 you'll get the string shown. The funcion will also parse from the end of the string, using a negative value for the 3rd parameter Occurrance. So, -1 is the last occurrance, -2 is the next to last, and so on. It returns an empty string if the occurrance of the delimited string does not exist. Public Function Parse1(ByVal ObjectString As String, ByVal DelimitString As String, ByVal Occurrance As Integer) As String Dim Count As Integer, Begin As Integer, Found As Integer, DelimitLength As Integer DelimitLength = Len(DelimitString) Parse1 = "" If Occurrance 0 Then Count = 1 Begin = 1 Do Found = InStr(Begin, ObjectString, DelimitString) If Found = 0 Then Exit Do Begin = Found + DelimitLength Count = Count + 1 Loop Occurrance = Count + Occurrance + 1 End If Count = 1 Begin = 1 Do While Count Occurrance Found = InStr(Begin, ObjectString, DelimitString) If Found = 0 Then Exit Function Begin = Found + DelimitLength Count = Count + 1 Loop Found = InStr(Begin, ObjectString, DelimitString) If Found = 0 Then Found = Len(ObjectString) + 1 Parse1 = Mid(ObjectString, Begin, Found - Begin) End Function Enjoy!!! (this line is not part of the code!) Tom Ellison -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
#3
|
|||
|
|||
Requested string function
Hi,
The Split function can help: ? Split("Now is the time for all good men to come to the aid of their country", "i") (3) d of the Note that the index starts at 0, not 1, so, here, 3 returns the fourth substring. Vanderghast, Access MVP "Tom Ellison" wrote in message ... I received a request for this function, which I had offered to provide in an earlier post here. Rather than send it just to the person who emailed me, I'm providing it here. Call the function with 3 parameters, none are optional. The first is a string to be searched. The second is a string (often just one character, or a CrLf) to search for, which I think of as the parse delimiter. The third is an integer showing which "substring" to return. Everything from the start of the string being searched, up to the string that parses it, is the first parsing. Starting after that first occurance of the delimit string is the second parsing, ending at the next occurance of the delimit string or the end of the string. The function is case sensitive. For example: "Now is the time for all good men to come to the aid of their country" Parse this using "i" 1 -5 Now 2 -4 s the t 3 -3 me for all good men to come to the a 4 -2 d of the 5 -1 r country What I've shown is the 5 substrings of the phrase being parsed, broken at each occurrance of "i". They are numbered to the left from 1 to 5. If your 3rd parameter is 1 to 5 you'll get the string shown. The funcion will also parse from the end of the string, using a negative value for the 3rd parameter Occurrance. So, -1 is the last occurrance, -2 is the next to last, and so on. It returns an empty string if the occurrance of the delimited string does not exist. Public Function Parse1(ByVal ObjectString As String, ByVal DelimitString As String, ByVal Occurrance As Integer) As String Dim Count As Integer, Begin As Integer, Found As Integer, DelimitLength As Integer DelimitLength = Len(DelimitString) Parse1 = "" If Occurrance 0 Then Count = 1 Begin = 1 Do Found = InStr(Begin, ObjectString, DelimitString) If Found = 0 Then Exit Do Begin = Found + DelimitLength Count = Count + 1 Loop Occurrance = Count + Occurrance + 1 End If Count = 1 Begin = 1 Do While Count Occurrance Found = InStr(Begin, ObjectString, DelimitString) If Found = 0 Then Exit Function Begin = Found + DelimitLength Count = Count + 1 Loop Found = InStr(Begin, ObjectString, DelimitString) If Found = 0 Then Found = Len(ObjectString) + 1 Parse1 = Mid(ObjectString, Begin, Found - Begin) End Function Enjoy!!! (this line is not part of the code!) Tom Ellison |
#4
|
|||
|
|||
Requested string function
Hello, John,
Not sure with which results you disagree. I never tired the function with 0 as the occurrance. Given the description of how it should work, zero doesn't make any sense to me. Being somewhat empirical about it, I really don't care what it returns. Not very "purist" and maybe not a good enough way to present a function for publication, but I was really trying to just get something working. The results for 1 and -4 are just what I intended. The result for -5 is wrong, of course. It should return an empty string. Given the situation I was in when I wrote the function, this wasn't an issue, but again, for publication, this isn't so great. I'll definitely give you that one. In the case of the data for which I wrote this, there was guaranteed to be at least 3 lines of data, and the delimiter was always CrLf. I never had any need to use negative values except -1 and -2, so having a negative value exceeding the number of parsings available didn't come up. I'm glad to know about this error, in case I ever need to use the function beyond the original requirements. The Split() function, I'm thinking, either wasn't around yet when I wrote this (98) or I wasn't yet aware of it. Maybe Split() makes it faster, but it's so nearly instantaneous as it is that doesn't bother me. Thanks, Tom "John Nurick" wrote in message ... Hi Tom, I don't think that all these results are what you intendedg: ?parse1("1,2,3,4", ",", 0) 1 ?parse1("1,2,3,4", ",", 1) 1 ?parse1("1,2,3,4", ",", -4) 1 ?parse1("1,2,3,4", ",", -5) 1 Here's my attempt: Function Parse2( _ ByVal V As Variant, _ ByVal Delimiter As String, _ ByVal Index As Long) As Variant 'Treats V as a string containing a list of items separated by Delimiter. 'Returns the Indexth item (first item is index 0). 'Negative index counts from end of list (last item is index -1) 'Returns Null if Index points to an item that's not in the list. Dim arItems As Variant arItems = Split(CStr(V), Delimiter) If Index = 0 Then If Index = UBound(arItems) Then Parse2 = arItems(Index) Else Parse2 = Null End If Else If Abs(Index) = UBound(arItems) + 1 Then Parse2 = arItems(UBound(arItems) + Index + 1) Else Parse2 = Null End If End If End Function On Wed, 14 Dec 2005 17:50:31 -0600, "Tom Ellison" wrote: I received a request for this function, which I had offered to provide in an earlier post here. Rather than send it just to the person who emailed me, I'm providing it here. Call the function with 3 parameters, none are optional. The first is a string to be searched. The second is a string (often just one character, or a CrLf) to search for, which I think of as the parse delimiter. The third is an integer showing which "substring" to return. Everything from the start of the string being searched, up to the string that parses it, is the first parsing. Starting after that first occurance of the delimit string is the second parsing, ending at the next occurance of the delimit string or the end of the string. The function is case sensitive. For example: "Now is the time for all good men to come to the aid of their country" Parse this using "i" 1 -5 Now 2 -4 s the t 3 -3 me for all good men to come to the a 4 -2 d of the 5 -1 r country What I've shown is the 5 substrings of the phrase being parsed, broken at each occurrance of "i". They are numbered to the left from 1 to 5. If your 3rd parameter is 1 to 5 you'll get the string shown. The funcion will also parse from the end of the string, using a negative value for the 3rd parameter Occurrance. So, -1 is the last occurrance, -2 is the next to last, and so on. It returns an empty string if the occurrance of the delimited string does not exist. Public Function Parse1(ByVal ObjectString As String, ByVal DelimitString As String, ByVal Occurrance As Integer) As String Dim Count As Integer, Begin As Integer, Found As Integer, DelimitLength As Integer DelimitLength = Len(DelimitString) Parse1 = "" If Occurrance 0 Then Count = 1 Begin = 1 Do Found = InStr(Begin, ObjectString, DelimitString) If Found = 0 Then Exit Do Begin = Found + DelimitLength Count = Count + 1 Loop Occurrance = Count + Occurrance + 1 End If Count = 1 Begin = 1 Do While Count Occurrance Found = InStr(Begin, ObjectString, DelimitString) If Found = 0 Then Exit Function Begin = Found + DelimitLength Count = Count + 1 Loop Found = InStr(Begin, ObjectString, DelimitString) If Found = 0 Then Found = Len(ObjectString) + 1 Parse1 = Mid(ObjectString, Begin, Found - Begin) End Function Enjoy!!! (this line is not part of the code!) Tom Ellison -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
UNC Names for splitted backend database | Veli Izzet | General Discussion | 9 | October 12th, 2005 02:07 PM |
Date & Time | mully | New Users | 4 | May 23rd, 2005 11:56 AM |
How do I use 3 cells to create the string for a lookup function? | Bencomo | Worksheet Functions | 1 | May 15th, 2005 07:17 AM |
Passing a Function (String) to a Where clause | Baby Face Lee | Running & Setting Up Queries | 1 | March 7th, 2005 11:36 PM |
Having trouble with multi-select list box in Access | brandelfly | New Users | 4 | February 10th, 2005 07:36 PM |