Two Different Characters in String

[SAP2]

Hello,
I am using Access 2003 and trying to create a query that separates the last
name from a field of last name/first name combo. My data can look like this

Jones, Adam

or can look like this:

Jones|Adam

I currently use Left(qryMHTracking!LName,InStr(qryMHTracking!LName,",")-1)
which does fine for the names with a comma in them. I cannot seem to find a
way where I can evaluate both instances (, and |) to yield the last name by
itself.

Any help?

 

[Mr. B]

SAP2,

Here is a public function that will do the evaluation:

Public Function GetLastName(strNameValue As String) As String
If InStr(1, strNameValue, ",") > 0 Then
GetLastName = Left(strNameValue, _
InStr(strNameValue, ",") - 1)
ElseIf InStr(1, strNameValue, "|") > 0 Then
GetLastName = Left(strNameValue, _
InStr(strNameValue, "|") - 1)
End If
End Function

You call the function like:

GetLastName (StringValueToBeEvaluated)

Replace the "StringValueToBeEvaluated" with a reference to the actual string
that you want to return the last name from. You could then create another
function like this one that would return only the First Nme or you could
modify the function to read both the Last Name and the First Name and then
concatenate the names back together in the desired format.


-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm