Extract LastName, FirstName, MI into three fields 1

[ThereUare]

I'm having a brain fart righ now. I can only do this to extract the LastName and First Name, but I also get the Middle Initial from the first name. I need something to only extract the First Name. Please Help. This is my formula so far.

This is the field I need to extract from:
LastName, FirstName MI

Formula: (Used in a Query)gets the firstname and MI
FName: Mid([FullName],InStr([FullName]," ")+1)

I need only the FirstName, but it would be nice if it sees the MI to insert it into a MI field.

 

[KenReay]

Have you looked at Split() Function, it may help

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[ThereUare]

Thanks but I know there is something easier I can just use in a query. I don't want to have to use code and look for specific events. It should just be a simple query.

 

[PHV]

FName: Trim(Mid(Left(FullName,InStrRev(FullName," ")),1+InStr(FullName,",")))
MI: Mid(FullName,InStrRev(FullName," ")+1)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ThereUare]

Wow that really helped. I was about to give up on it. Thank you very much for your Help PHV.

I was wondering if you had something in case the FullName did not have a middle initial? It seems to work very good until you come up with a name without a middle initial.