Parsing a string
From: Ted Allen (anonymous_at_discussions.microsoft.com)
Date: 04/22/04
- Next message: Jean: "IS null"
- Previous message: RCavallaro_at_beainn.com: "Trying to remove very similar (not quite duplicate) records"
- In reply to: anonymous_at_discussions.microsoft.com: "Parsing a string"
- Next in thread: Mark G. King: "Re: Parsing a string"
- Reply: Mark G. King: "Re: Parsing a string"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 22 Apr 2004 07:20:38 -0700
Hi TJ,
Sorry for the delayed response, this has been a hectic
week at work.
Listed at the end of this message is the code for the
function that I use (watch the line wrapping). You can
paste this code (repair the wrapping though) and then
call it in a query such as ParseArray
([FieldToBeParsed],3) - which would return the 3rd field.
The function also has two optional input parameters, one
specifying the ASCII code for the delimeter (mine is a
pipe which has the code 10), and another to specify
whether to add a prefix with the field number and a dash.
If you used the function, you would want to change the
default delimiter value in the function definition line
to match your case. If you don't use the code, you can
use the Asc() function or look it up in the character map
in help.
I put together this function as a test sometime back and
never really went back over it to see if I could have
made it more efficient. In looking at it now I think it
is likely it could be tweaked to improve performance,
such as possibly using the replace function to get rid of
all of the delimiters before the one that you are
interested in, then using the instr() functions. But, it
did work fine when I tested it.
I didn't have the errorhandler in testing, but just added
it. I tried a new approach with the error handler here
which is just to return the error information to the
function call if an error is encountered. I did this
because otherwise if you test a function with a lot of
records and the function encounters an error, it keeps
notifying you for each record. In the past to avoid this
I would set the top values property of the query to 5
during testing, but I think this error handler should
prevent this.
Anyway, following is the function code. To use it just
create a new module and past the function in. Then, you
can type the function in queries, or you can insert it
using the builder if you go to functions and then click
on the name of your database. Post back if you have any
questions.
Public Function ParseArray(InputArrayField, OutputFieldNo
As Integer, Optional DelimiterCharCode As Integer = 10,
Optional InclFieldNo_Y_N As String = "N")
Dim ArrayBreakPos() As Integer, strPrefix As String
On Error GoTo ErrorHandler
If IsNull(InputArrayField) = True Then
ParseArray = Null
Exit Function
End If
If UCase(Left(InclFieldNo_Y_N, 1)) = "Y" Then
strPrefix = OutputFieldNo & " - "
Else
strPrefix = ""
End If
ReDim ArrayBreakPos(OutputFieldNo) As Integer
'Set the Break Position 0 (which is not a real break) as
0 to start searching the string initially
'At the start point of the string (1 character after the
previous break pos)
ArrayBreakPos(0) = 0
For i = 1 To OutputFieldNo
ArrayBreakPos(i) = InStr(ArrayBreakPos(i - 1) + 1,
InputArrayField, Chr(DelimiterCharCode), vbTextCompare)
If ArrayBreakPos(i) = 0 Then
If i < OutputFieldNo Then
ParseArray = Null
Else
ParseArray = strPrefix & Right
(InputArrayField, Len(InputArrayField) - ArrayBreakPos
(OutputFieldNo - 1))
End If
Exit Function
End If
Next
ParseArray = strPrefix & Mid(InputArrayField,
ArrayBreakPos(OutputFieldNo - 1) + 1, ArrayBreakPos
(OutputFieldNo) - ArrayBreakPos(OutputFieldNo - 1) - 1)
Exit Function
ErrorHandler:
ParseArray = "Error - " & Err.Number & " " &
Err.Description
End Function
Hope that helps.
-Ted Allen
>-----Original Message-----
>Thanks again, Ted. I don't know why my post didn't
appear
>in the newsgroup. I found a somewhat complicated set of
>functions that will locate the nth delimeter, but I am
>VERY interested in your function if it is at all less
>cumbersome than what I've found.
>
>>-----Original Message-----
>>Hi TJ,
>>
>>I'm not sure if you'll check back for this, but I just
>>saw your response to my previous post in a Google
search -
>> it doesn't show up in the microsoft newsreader
though.
>>Pasted below is your post:
>>
>><Thanks Ted. I was able to locate a KB article relating
>><to InStrRev that states it can not be used as an
>><expression due to a know Access bug. Once I created a
>><function it worked great.
>><
>><As a follow up, how would I select the nth delimeter
in
>><a string? I.E. if I had a string of 4 or 5 strings
>><separated by a delimeter is there a way to select,
say,
>><the 2nd instance of a delimeter?
>><
>><TJ
>>
>>You can do this in a few ways. For simple parses of a
>>handful or so fields, you can use nested Instr()
>>functions. To do this, you would use the Instr()
>>function to specify the start position for another Instr
>>() function (plus 1). You would then use the Mid
>>function to extract the string (from the previous Instr
()
>>position).
>>
>>Another way, which will take a little longer initially,
>>but provides more flexibility, would be to write a
custom
>>funtion. I have written one that can be used similar
to
>>the Instr() function, but it has an input parameter for
>>the field number that you are looking for. I use it to
>>parse an Oracle array field, and thus it defaults to
>>using the pipe character as the delimiter, but has an
>>optional input for another delimiter. I could post the
>>code if you are interested in this. Or, I'm sure there
>>are pre-written custom functions at one or more of the
>>useful Access Libraries such as the Access Web or
Roger's
>>Access Library.
>>
>>Hope this helps.
>>
>>-Ted Allen
>>>-----Original Message-----
>>>Try using InStrRev, in combination with the Right()
and
>>>Len() functions.
>>>
>>>HTH, Ted Allen
>>>>-----Original Message-----
>>>>I need to parse a string to return all characters
after
>>>>the LAST instance of a delimiter. I know how to use
the
>>>Mid
>>>>() function along with the Instr() function to locate
>>>the
>>>>first instance of a character, but how do I find the
>>>last?
>>>>I found a reference to Reverse() function but that
>>>creates
>>>>an undefined function error. I'm using Access 2000.
>>>>
>>>>Sample strings:
>>>>1. John, Adam, Robert
>>>>2. Joshua, Michael, Alan
>>>>
>>>>I need to return the last name listed in each string.
>>>>.
>>>>
>>>.
>>>
>>.
>>
>.
>
- Next message: Jean: "IS null"
- Previous message: RCavallaro_at_beainn.com: "Trying to remove very similar (not quite duplicate) records"
- In reply to: anonymous_at_discussions.microsoft.com: "Parsing a string"
- Next in thread: Mark G. King: "Re: Parsing a string"
- Reply: Mark G. King: "Re: Parsing a string"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
