Parsing a string

[TJ]

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.

 

[Ted Allen]

Try using InStrRev, in combination with the Right() and
Len() functions.

HTH, Ted Allen

 

[Guest]

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

 

[Ted Allen]

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

 

[Guest]

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.

 

[Ted Allen]

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

 

[Mark G. King]

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

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.

.