lastname firstname

[shank]

I have a list of names that show as: LASTNAME FIRSTNAME
There's no punctuation whatsoever.
I need to get them into FIRSTNAME LASTNAME order.
How can I query this?
I guess the best way is to take everything prior to the first space as
LASTNAME. Then take everything after the first space, and stick the LASTNAME
on the end.

thanks!

 

[Guest]

Yes, but some last names contain a space such as De Learn
or Van Goeugh. Also it is always best to put them in two
different fields. Use two more fields for middle initial
and suffix like JR, SR, II, III, etc.

 

[shank]

I'm aware of that. This is not my list. This is a list that I acquired and
need to groom as FIRSTNAME LASTNAME. There will be obvious problems that
must be manually dealt with. But a query can zip through many thousands a
lot easier than me.

 

[Brendan Reynolds]

SELECT Mid$([FullName],InStr(1,[FullName]," ")+1) & " " &
Left$([FullName],InStr(1,[FullName]," ")-1) AS MyName
FROM tblTest;