Query to remove middle initial

[tomlebold]

Look for query that changes Tom L LeBold into Tom LeBold and only when
the middle intial exist.

[fredg]

On 12 Apr 2007 09:29:59 -0700, to*******@msn.com wrote:

Look for query that changes Tom L LeBold into Tom LeBold and only when
the middle intial exist.

If there is ALWAYS a period after the middle initial, you could use

NoInitial:
IIf(InStr([FieldName],".")>0,Left([FieldName],InStr(FieldName],".")-2)
& Mid([FieldName],InStr([FieldName],".")+2),[FieldName])

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Chuck]

On 12 Apr 2007 09:29:59 -0700, "to*******@msn.com" <to*******@msn.comwrote:

>Look for query that changes Tom L LeBold into Tom LeBold and only when
the middle intial exist.

What happens if Tom E LeBold is added later?

Chuck
--

[CDMAPoster]

On Apr 12, 11:29 am, "[e-mail]" <[e-mail]:

Look for query that changes Tom L LeBold into Tom LeBold and only when
the middle intial exist.

Maybe just grab what's before the first space, if it exists, and
concatenate it with what's after the last space:

IIf(InStr(FieldName, ' ') 0, Left(FieldName, InStr(FieldName, ' ') -
1) & ' ' & Right(FieldName, Len(FieldName) - InStrRev(FieldName, '
')), Null)

Note that the InStrRev function does not exist in Access 97. A
replacement function written by John Viescas and Dirk Goldgar can be
found here:

http://groups.google.com/group/comp....b7cd432f7fb2c3

This expression worked on the few examples I tried along with the
replacement function above. Note that it will not do what you want if
the first or last name contains a space. Maybe the expression above
is adequate for your needs.

Note: The first time I used InStrRev I didn't realize that the index
stayed in the same direction as before. Albert Kallal kindly pointed
out my error in a private email. Without the InStrRev function a user-
defined function such as ReverseString can also be used in Access 97
to get the same results in a slightly less elegant fashion.

James A. Fortune
CD********@FortuneJames.com