Link to home
Create AccountLog in
Avatar of nbotts
nbotts

asked on

Removing middle name from name field

Hello,

I have a name field that lists names in the following manner: last,first middle

I need to change it so that it lists names like this: last, first

I am using the following to add a space between the last and first name, but am uncertain how to remove everything that follow after the first name:

NameWithSpacenoMiddle: Format(Replace([Name_Table.Name],",",", "),"""text""")

Thanks!
Avatar of Cluskitt
Cluskitt
Flag of Portugal image
Replace(Left([Name_Table.Name],InStr([Name_Table.Name]," ")-1),",",", ")

Basically, instead of replacing the whole field, you're going to be replacing just the part of it that is to the left of the first space. This, however, assumes that you ALWAYS have a space in the field.
Avatar of nbotts
nbotts

ASKER

Thanks Cluskitt that generally works, but it does appear that some of the names do not have a middle name and when that is the case it throws an #Error.

Any way to control for if there is no middle name (and so I am assuming no space?
ASKER CERTIFIED SOLUTION
Avatar of Cluskitt
Cluskitt
Flag of Portugal image
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image
try this

NameWithSpacenoMiddle: Left([Name_Table.Name],InStr([Name_Table.Name],",")) & " " & Mid([Name_Table.Name],InStr([Name_Table.Name],",")+1,Trim(InStrRev([Name_Table.Name]," ")-InStr([Name_Table.Name],",")))
should give you

Last, First
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image
to correct the error of missing space on some names


try this

NameWithSpacenoMiddle: Left([Name_Table.Name],InStr([Name_Table.Name],",")) & " " & Mid([Name_Table.Name],InStr([Name_Table.Name],",")+1,Trim(InStrRev([Name_Table.Name] & " "," ")-InStr([Name_Table.Name],",")))
should give you

Last, First
Avatar of nbotts
nbotts

ASKER

Thanks Cluskitt, that did the trick. Much appreciated!
Avatar of nbotts
nbotts

ASKER

Thanks as well capricorn1.

Unfortunately the solution you provided still gave me an #Error whenever a middle name wasn't present.
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image
nbotts,


did you try my second post at http:#a38032272
Avatar of Cluskitt
Cluskitt
Flag of Portugal image
Actually, his query should work for those with no space, but would generate an error for those without a comma (,)