Expression to middle name/initial from "Last, First Middle"

[Guest]

I am trying to write an expression for an update query, that will capture the
middle name/initial from a [Name] field that is formatted as "Last, First
Middle/MI". The problem is that there is not always a middle name/initial. So
some data is formatted as "Last, First". I need it to capture the middle
name/initial when it occurs and nothing when it doesn't.

I need it to do this:
Original Entry in [Names]: "Doe, John P."
Returned by Expression: P.
Expression: Expr: Right(Trim([Names]),Len(Trim([Names]))-InStr _
(InStr(1, [Names]," ")+1,[Names]," "))

But like this (leaving the field blank when there is no middle name):
Original Entry in [Names]: "John Doe" or "John P. Doe"
Returned by Expression: P. or blank
Expression: Expr: Trim(Mid([names], InStr(1, [names], " ") + 1,
IIf(InStr(InStr(1, [names], " ") + 1, [names], " ") = 0, 0, InStr(InStr(1,
[names], " ") + 1, [names], " ") - InStr(1, [names], " "))))


Thanks
Tim

 

[Jeff Boyce]

Tim

Are you ABSOLUTELY certain that every name with a Middle Initial has a
period (.) after it?

If so, any namefield that had Right([NameField],1) = "." should have an
initial and a period in the last two places. So your test would be
something like:
if there is no period, blank,
otherwise, get the last two and use the first one

Perhaps you could approach this backwards?

Good luck

Jeff Boyce
<Access MVP>