I've found some similar threads, but none with an answer so forgive my repetition.
My scenario so far:
I have to import this tragically misconfigured Excel document to populate a personnel table. If anyone has a better solution on fixing this at import, that would be awesome! However, as I have it now--The names only follow these formats...
BALDWIN, BILLY S (or)
BALDWIN, BILLY SAM
BALDWIN, BILLY S JR
BALDWIN, BILLY SAM II
Using my code I can succeed in getting LastName, FirstName, and MiddleName but ONLY if its a single character middle initial. I just need desperately to have LastName, FirstName, and Middle initial without any of the extra characters or Jr/II/III/etc.
My Access SQL reads:
UPDATE tbPersonnel SET
tbPersonnel.LastName = Left(FULL_NAME,Instr(1,FULL_NAME & ",",",")-1),
MiddleName = IIF(Right(FULL_NAME,2) Like " *",Right(FULL_NAME,1),NULL),
FirstName = TRIM(LEFT(Mid(FULL_NAME,Instr(1,FULL_NAME,", ")+1),Instr(Mid(FULL_NAME,Instr(1,FULL_NAME," ")+1) & " "," ")));
Also, anyone have a short answer on how to turn it proper noun easily in this process or in Access? I'm pretty new to Access, but if its a hassle, no biggie.
Any ideas? Thanks in advance! Bryan