update query to add a space

[ghudson]

I need to run an update query to add a space after a comma if the space does not already exists. The data will look like this...

Doe, John
Smith,Bob

The [Doe, John] values needs to be ignored and the [Smith,Bob] values need to be updated to [Smith, Bob].

Any suggestions on how I can do this with an update query?

Thanks in advance for your help!

 

[pdx_man]

Write a function like this:

Public Function AddSpace(MyStr As String) As String

    If InStr(MyStr, " ") = 0 Then
        MyStr = Left(MyStr, InStr(MyStr, ",")) & " " & Mid(MyStr, InStr(MyStr, ",") + 1)
    End If
    AddSpace = MyStr

End Function

... and reference it from the Update To row for the name field, passing it the name field.

*** EDIT ***
I just got to thinking ... if your name had spaces in it, like Van Buren,Martin ... this would fail, so we may want to test the character after the comma (Like you said ):

If Mid(MyStr, InStr(MyStr, ",") + 1, 1) <> " " Then

 

[ghudson]

Thank you! Works great!

You were correct about testing for the space after the comma.