Name Parsing

[Michael SF]

I have a name like this

RORER, SALLY M

I need to be able to IN A QUERY parse this out into three fields

First - SALLY
Last - RORER
MI - M

How can i do that as well as if they dont have a middle name put a " " in
it.

Thanks

 

[John Nurick]

Hi Michael,

Use three calculated fields in your query, with an expression in each
that uses VBA functions such as IIf(), InStr(), Mid() and Left() to
extract the relevant portions of the name. E.g. something like

LastName: Left([XXX], IIf(Instr([XXX], ",") > 0, InStr([XXX], ",") -
1), Len([XXX]))

Sometimes, instead of developing complex expressions for a calculated
field, it's simpler to use a custom VBA function to do the hard work and
call the function from the calculated field. The expression above, with
a bit more safeguarding, becomes this function (untested air code):

Public Function ExtractLastName(FullName As Variant) As Variant
Dim S as String

If IsNull(FullName) Then 'Field is empty
ExtractLastName = Null
Exit Function
End If

S = CStr(FullName)
If InStr(S, ",") = 0 Then
'No comma found, return whole name
ExtractLastName = S
Else
ExtractLastName = Left(S, InStr(S, ",") - 1)
End If
End Function