Pulling text out of a field

[lauriesamh]

Hi,
I have in table two fields First Name (FNAME) and Middle Initial (MI). In some case the MI is populated. When it is not then the true middle initial is occasionally typed in FNAME with a space " " then the Middle initial example:"Jane M". I want to pull the "M" and place it into a new field (MI corrected). This is what I'm using below. My problem is that when the (MI) is not populated and the FNAME has no middle initial the new (MI Corrected) field populates what is in the FNAME. Any assistance would be appreciated.

MI Corrected: IIf([Member Middle Name] Is Null,Right([Member First Name],Len([Member First Name])-InStrRev([Member First Name]," ",-1)),[Member Middle Name])

 

[traingamer]

one way:

MI Corrected: IIf(([Member Middle Name] Is Null)[COLOR=blue] and (InStr([Member First Name]," ")>1)[/color],Right([Member First Name],Len([Member First Name])-InStrRev([Member First Name]," ",-1)),[Member Middle Name])

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[lauriesamh]

Thanks worked like a charm! Kudos to you!

 

[lauriesamh]

Followup question.. how would I parse the first name only? IE take the Middle Intial out of the First Name and put that result into a FN Corrected field

 

[HarleyQuinn]

Apply very similar principles, something like:

FN Corrected : IIf(InStr([fname]," ")>0,Left([fname],InStr([fname]," ")-1),[fname])

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[lauriesamh]

Thanks! Worked very well.

 

[HarleyQuinn]

Glad to help

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.