Parsing out Initial and period Only

[TotallyConfused]

Can someone please help me finish this query? I have a list of providers
that can look something like this:

Clooney, George A., MD
AnyName Pediatrics
Grace, Kelly, DO

etc. I am trying to parse out the name. I have the First Name, Last Name
and Degree. But I can't seem to parse out the Middle Initial for the Initial
Field, eliminate the MD or DO and leave blank if no MD or DO. This is what I
have I have written several variations but can't make it work. Can someone
please help me with this? Thank you very much in advance.

NITALONLY: Trim(IIf([PROV NM] Like "*, DO" Or [PROV NM] Like "*,
MD",Right(Trim([PROV NM]),Len(Trim([PROV NM]))-InStr(InStr(2,[PROV NM],"
")+1,[PROV NM]," ")),Null))

 

[MGFoster]

Can someone please help me finish this query? I have a list of providers
that can look something like this:

Clooney, George A., MD
AnyName Pediatrics
Grace, Kelly, DO

etc. I am trying to parse out the name. I have the First Name, Last Name
and Degree. But I can't seem to parse out the Middle Initial for the Initial
Field, eliminate the MD or DO and leave blank if no MD or DO. This is what I
have I have written several variations but can't make it work. Can someone
please help me with this? Thank you very much in advance.

NITALONLY: Trim(IIf([PROV NM] Like "*, DO" Or [PROV NM] Like "*,
MD",Right(Trim([PROV NM]),Len(Trim([PROV NM]))-InStr(InStr(2,[PROV NM],"
")+1,[PROV NM]," ")),Null))

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Here's a function I call from my queries. Put it in a standard VBA
module.

Function GetMiddleName(strName As String, _
Optional strSeparator As String = ",") As Variant
' Purpose:
' Get the middle name off the indicated string
' In:
' strName The indicated string.
' Format: "LastName,FirstName MiddleName"
' strSeparator The character that separates the LastName
' and FirstName.
' Out:
' Variant The middle name. If no middle name - a NULL.
' Created:
' mgf 15apr2003
' Modified:
'

' This function can be called from a query
On Error Resume Next

Dim varTemp As Variant

varTemp = Trim$(Mid$(strName, InStr(1, strName, strSeparator) + 1))

If InStrRev(1, varTemp, " ") > 0 Then
varTemp = Trim$(Mid$(varTemp, InStr(1, varTemp, " ") + 1))
Else
varTemp = Null
End If

If varTemp = "" Then varTemp = Null

GetMiddleName = varTemp

End Function

Since it works on strings that do not have a space between the last name
and the first name you'll have to use a Replace(), to get rid of the
extra space, when calling it from the query. Since you want to get rid
of the "MD" and "DO" you can use more Replaces to do that.

SELECT GetMiddleName(Replace(Replace(Replace([Prov Nm], ", ", ","), ",
MD", ""),", DO","")) As MidInit,
... <other columns> ...
FROM ...
.... etc. ...

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSnfhR4echKqOuFEgEQLsxQCg8zpLWJLop4eTaUpwQ75k/95KF90AoJ/6
aTCsh49GBWhZHgHnxWx9vHyK
=wb4V
-----END PGP SIGNATURE-----