Replace Function

[Rozza]

Hi,

I want to be able to replace spaces in a persons name with commas eg Mr Paul Rowling would become Mr,Paul,Rowling.
I know that there is a function called replace which should do the job, but I'm unsure how to use it and wether it can be done from within a query?
I'm not a programmer by any means which is why I'm struggling.

Any help would be greatly appreciated.

Cheers

Paul

 

[PaulBricker]

This function
Replace("Mr Paul Rowling"," ",",",,,vbTextCompare)
will return
Mr,Paul,Rowling


Paul

 

[stduc]

Feel free to incorporate the following code in your own module. I would appreciate it though if you don't change it in any way - without letting me know.

Call it as follows.

NewString=fx_ReplaceChar(CurrentString," ",",&quot


'
'=====================================================================
'
' Function Name : fx_ReplaceChar
'
' Description : Replace all occurrances of a substring in a string with another substring.
'
' Input Parameters : Text$ - String in which to substitute substring
' : TextToFind$ - Substring to be replaced
' : TextToReplace$ - Substring to replace
'
' Return Value : Substituted String
'
' Calls : Mid$
'
'---------------------------------------------------------------------
'
' Author: : Stephen J Duckworth
' Date: : May 1999
' Company : Bournville Consultants Limited
' Email : Support@Bville.clara.net
'
'---------------------------------------------------------------------
'
' Change History:
'
' Version Date Author Description
' --------------------------------------------------------------------
' 1.0 07/05/99 SJD Initial version
' 2.0 20/06/02 SJD Remove length restrictions
'=====================================================================
'
Function fx_ReplaceChar(Text$, TextToFind$, TextToReplace$) As String

Dim LT As Long, LR As Long, LF As Long
Dim PF As Long, TS$

On Error GoTo err_fx_ReplaceChar

LT = Len(Text$)
LR = Len(TextToReplace$)
LF = Len(TextToFind$)
TS$ = Text$

If TextToFind$ = TextToReplace$ Then GoTo exit_fx_ReplaceChar

Do While InStr(Text$, TextToFind$) > 0

PF = InStr(Text$, TextToFind$)

Text$ = Mid(TS$, 1, PF - 1) & TextToReplace$ & Mid(TS$, PF + LF, Len(TS$))
LT = Len(Text$)
LR = Len(TextToReplace$)
LF = Len(TextToFind$)
TS$ = Text$

Loop

exit_fx_ReplaceChar:

fx_ReplaceChar = Text$

Exit Function

err_fx_ReplaceChar:

Resume exit_fx_ReplaceChar

End Function

 

[Rozza]

Hi Paul,

How do I incroporate the line you've given me into the database to perform the action I want? Can I put this in a query? Also, can the String "Mr Paul Rowling" be replaced with a field name which store peoples names?

Best regards


Paul Rowling

 

[PaulBricker]

In a new column of the query put something like this
MyDelimitedField:Replace([FieldNameHere]," ",",",,,vbTextCompare)

Insert your field name and that will do it.

Paul

 

[Rozza]

Thanks alot, I'll give it a try!!