find and replace the first space in a field with a comma

[stever1725]

help please.. how can i find and replace the first space in a field with a comma. i have an update query with : replace([GTR NAME]," ",",") in the update to field... this field has more than 1 space, i just want to replace the first one.

 

[traingamer]

If you look at the help file you'll see that the options for replace are:

Replace(expression, find, replace[, start[, count[, compare]]])

Try: replace([GTR NAME]," ","," [blue], 1, 1[/blue])

Which says starting with the first position, replace the first instance of a space with a comma.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[randy700]

There's probably an easier method, but this will work...

Public Function Space2Comma()
    Dim strSQL As String
    Dim intSpace As Integer
    strSQL = "John Q. Public"
    intPlace = InStr(1, strSQL, " ")
    strSQL = Left(strSQL, intSpace - 1) & "," & Right(strSQL, Len(strSQL) - intSpace)
    Debug.Print strSQL   'prints John,Q. Public
End Function

Randy

 

[stever1725]

i pasted this: replace([GTR NAME]," ","," , 1, 1) into the update to... but no commas have been added in the field.... what am i doing wrong?

 

[randy700]

sorry for the typo

Public Function Space2Comma()
    Dim strSQL As String
    Dim intSpace As Integer
    strSQL = "John Q. Public"
    intSpace = InStr(1, strSQL, " ")
    strSQL = Left(strSQL, intSpace - 1) & "," & Right(strSQL, Len(strSQL) - intSpace)
    Debug.Print strSQL   'prints John,Q. Public
End Function

Randy

 

[stever1725]

Greg, i had one too many spaces for the "find".. it works, thanks.
Randy, thanks also.

 

[stever1725]

Randy- if i wanted to use this function in a macro-RunCode.. would i would have to call the table first and then the function ??

 

[randy700]

I only built this function to demonstrate how to perform the task you required. I suspect to use it in a macro, you would have to modify it to provide the source data.


Randy

 

[lespaul]

Does the Replace function not work for you using the extra parameters?



Leslie

Have you met Hardy Heron?