Re: Parsing a string
From: Mark G. King (markgking_SpamTrap_at_hotmail.com)
Date: 04/24/04
- Next message: Steve Schapel: "Re: sum function"
- Previous message: Michele Zenna: "Re: Query SQL"
- In reply to: Ted Allen: "Parsing a string"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 24 Apr 2004 16:46:42 +1000
If you want to parse name strings in code take a look at a .DLL designed for
this job
It's a class library you can reference in your database and call its methods
to parse names.
e.g. GetFirstname("President George W. Bush") returns "George"
http://www.infoplan.com.au/nameparser
- Mark
"Ted Allen" <anonymous@discussions.microsoft.com> wrote in message
news:2b3d01c42874$fbf325b0$a601280a@phx.gbl...
> 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: Steve Schapel: "Re: sum function"
- Previous message: Michele Zenna: "Re: Query SQL"
- In reply to: Ted Allen: "Parsing a string"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
