First, middle and last name

[DavyLad]

I have a list if names, some with a middle initial and some without, that are all in one field. eg George P Wingrove or Ada Wingrove. Can I run a query that will seperate the name into two or three fields? I have tried the following in a query, but not being very successful. FirstName:Left([Name],InStr(1,[Name]," ")-1), MiddleInitialTrim(Mid([Name],InStr(1,[Name]," ")+1,InStr(InStr (1, [Name], " ")+1,[Name]," ")-InStr(1,[Name]," "))) and LastName:IIf(InStr(InStr([Name]," ")+1,[Names]," ") <>0, Right([name],Len([name])-InStr(InStr([Name]," ")+1,[Name]," ")),Right([Name],Len([Name])-InStr([Name]," ")))
Unfortunately, it puts #Error if there is no middle initial. Any way round this problem?

David

 

[KeithG]

You will have to write a custom VBA function to parse the data.

 

[neileg]

Names are always tricky. What if you have a William P van der Post?

 

[Brianwarnock]

If there is more than just the 2 names can be tested for

If instr(field," ") = instrrev(field," ") then there is only the 2 names, but as Keith said you are best writing a function, I think there may be one somewhere in the forum as similar questions have been asked.

Brian

 

[DavyLad]

Thanks all, I will have a look to try to find it.

David

 

[jdraw]

Thanks all, I will have a look to try to find it.

David

Here's a link to a KB article on names and parsing. It may be of some assistance.

http://support.microsoft.com/kb/q115915/